﻿#region Copyright & Revision History
/*
 * This file contains the source code for Open xml
 * Infosys Technologies, all rights reserved.
 * 
 * Revision History
 * No.	Author		Date    		Remarks
 * 1	Karthik		01 Mar 13	    Writing functionalities of open xml
 * 2    Karthik		23 Jul 13	    Added Reading functionality(Read range from excel)  
 * 3    Karthik		13 aug 13	    fixed issue  reading junk value
 * 
 */
#endregion
using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml;
using System.Collections;
using System.Text.RegularExpressions;
using System.IO.Packaging;
using System.Xml;
using System.Xml.XPath;
using System.Globalization;
using System.Reflection;




namespace AutoTestingFramework.Utilities
{
    internal class OpenXmlExcelHelper : IDisposable
    {
        private SpreadsheetDocument _excelDoc = null;
        private WorkbookPart _workBookPart = null;
        private WorksheetPart _worksheetPart = null;
        private SheetData _sheetData = null;
        private Stylesheet _styleSheet = null;
        private Dictionary<Fill, uint> fillTable = null;
        private Dictionary<Font, uint> fontTable = null;
        private Dictionary<Border, uint> borderTable = null;

        #region Private Fields
        private Package m_package;
        DataTable dtable;
        XmlNamespaceManager nsMgr = null;
        SpreadsheetDocument document = null;
        #endregion


        public WorkbookPart WorkBookPart
        {
            get { return _workBookPart; }
            set { _workBookPart = value; }
        }

        public WorksheetPart WorkSheetPart
        {
            get { return _worksheetPart; }
            set { _worksheetPart = value; }
        }

        /// <summary>
        /// Contains all the styling information
        /// </summary>
        public Stylesheet StyleSheet
        {
            get
            {
                if (_styleSheet == null)
                {
                    _styleSheet = _excelDoc.WorkbookPart.WorkbookStylesPart.Stylesheet;
                    uint fillCount = 0;
                    uint fontCount = 0;
                    uint borderCount = 0;

                    fillTable = _styleSheet.Fills.Elements<Fill>().ToDictionary(k => k, v => fillCount++);
                    fontTable = _styleSheet.Fonts.Elements<Font>().ToDictionary(k => k, v => fontCount++);
                    borderTable = _styleSheet.Borders.Elements<Border>().ToDictionary(k => k, v => borderCount++);

                    return _styleSheet;
                }
                return _styleSheet;
            }
            set
            {
                _styleSheet = value;
                if (value == null)
                {
                    fillTable = null;
                    fontTable = null;
                    borderTable = null;

                }
                else
                {
                    uint fillCount = 0;
                    uint fontCount = 0;
                    uint borderCount = 0;

                    fillTable = _styleSheet.Fills.Elements<Fill>().ToDictionary(k => k, v => fillCount++);
                    fontTable = _styleSheet.Fonts.Elements<Font>().ToDictionary(k => k, v => fontCount++);
                    borderTable = _styleSheet.Borders.Elements<Border>().ToDictionary(k => k, v => borderCount++);

                }
            }
        }

        public static List<string> GetPropertyInfo<T>()
        {
            PropertyInfo[] propertyInfos = typeof(T).GetProperties();
            // write property names
            return propertyInfos.Select(propertyInfo => propertyInfo.Name).ToList();
        }
              

        /// <summary>
        /// 
        /// </summary>
        public SheetData SheetData
        {
            get { return _sheetData; }
            set { _sheetData = value; }
        }

        /// <summary>
        /// Opens a Excel file
        /// </summary>
        /// <param name="filename">FileName with path</param>
        /// <param name="isEditable">True,False</param>
        public void ExcelName(string filename, bool isEditable)
        {
            try
            {
                
                _excelDoc = SpreadsheetDocument.Open(filename, isEditable);
                WorkBookPart = _excelDoc.WorkbookPart;
                StyleSheet = _excelDoc.WorkbookPart.WorkbookStylesPart.Stylesheet;
                WorkSheetPart = null;
                SheetData = null;
                m_package = _excelDoc.Package;
            }
            catch (OpenXmlPackageException opEx)
            {
                throw opEx;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Opens a Excel file
        /// </summary>
        /// <param name="filename">FileName with path</param>
        /// <param name="isEditable">True,False</param>
        /// <param name="openSetting">Settings</param>
        public void ExcelName(string filename, bool isEditable, OpenSettings openSetting)
        {
            try
            {
                _excelDoc = SpreadsheetDocument.Open(filename, isEditable, openSetting);
                StyleSheet = _excelDoc.WorkbookPart.WorkbookStylesPart.Stylesheet;
                WorkSheetPart = null;
                SheetData = null;
                m_package = _excelDoc.Package;
            }
            catch (OpenXmlPackageException opEx)
            {
                throw opEx;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        void EmbedObject(string SheetName, string CellRef, string EmbedObjectFilePath, string Caption)
        {
            //todo: Add logic
            throw new NotImplementedException();
        }

        /// <summary>
        /// Selects Worksheet to be accessed
        /// </summary>
        /// <param name="worksheetId">Name of the WorkSheet(Case sensitive)</param>
        public bool SelectWorkSheetByName(string worksheetName)
        {
            if (string.IsNullOrEmpty(worksheetName))
            {
                //GetDbData.sbEventLog.Append("\n sheet name is null");
                //Microsoft.ApplicationBlocks.ExceptionManagement.ExceptionManager.Publish(new Exception("Sheet name is null"));
                return false;
            }
            IEnumerable<Sheet> sheet = _excelDoc.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>().Where(s => s.Name == worksheetName);
            if (sheet.Count() == 0)
            {
                OpenXmlPackageException openEx = new OpenXmlPackageException("WorkSheets not found");
                //ExceptionManager.Publish(openEx);
                return false;
            }

            string relationshipId = sheet.FirstOrDefault().Id.Value;
            WorkSheetPart = (WorksheetPart)_excelDoc.WorkbookPart.GetPartById(relationshipId);
            if (WorkSheetPart == null)
            {
                OpenXmlPackageException openEx = new OpenXmlPackageException("WorkSheets not found");
                //ExceptionManager.Publish(openEx);
                return false;
            }

            SheetData = WorkSheetPart.Worksheet.Elements<SheetData>().FirstOrDefault();

            if (SheetData == null)
            {
                OpenXmlPackageException openEx = new OpenXmlPackageException("WorkSheets not found");
                //ExceptionManager.Publish(openEx);
                return false;
            }

            return true;
        }

        # region  Methods of OPENXML Excel Implementation

        /// <summary>
        /// Inserts Data from DataTable into excel
        /// </summary>
        /// <param name="excelData">Data Table containing Data</param>
        /// <param name="rowIndex">If not specified writes from 1st Row</param>
        public void InsertDataTableIntoExcel(DataTable excelData, int rowIndex = 1)
        {
            if (_excelDoc != null && SheetData != null)
            {
                if (excelData.Rows.Count > 0)
                {
                    try
                    {
                        uint lastRowIndex = (uint)rowIndex;
                        //each datacell  value is inserted into the  from the specified row index
                        for (int row = 0; row < excelData.Rows.Count; row++)
                        {
                            //returns the row if exists else adds a new row and returns
                            Row dataRow = GetRow(lastRowIndex, true);
                            for (int col = 0; col < excelData.Columns.Count; col++)
                            {
                                // gets the cell object,if exists else creates a cell objects and returns the object from the data row 
                                Cell cell = GetCell(dataRow, col + 1, lastRowIndex);

                                //inserts the datacell value into excel cell
                               CellValueInsert(cell, excelData.Rows[row][col]);
                              /*  //CellValueInsert(cell, excelData.Columns[col].DataType);
                               // CellValueInsertTEST(cell,excelData.Rows[row][col] ,excelData.Columns[col].DataType.ToString());*/
                            }
                            lastRowIndex++;
                        }
                    }
                    catch (OpenXmlPackageException ex)
                    {
                        throw ex;
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                }
                else
                {
                    OpenXmlPackageException openEx = new OpenXmlPackageException("No data from datatable");
                    throw openEx;
                }
            }
            else
            {
                OpenXmlPackageException openEx = new OpenXmlPackageException("Workbook not found");
                throw openEx;
            }
        }

        /// <summary>
        /// Inserts Data from DataTable into excel
        /// </summary>
        /// <param name="excelData">DataTable Param</param>
        /// <param name="cellRef">Cell Reference to start writing data</param>
        public void InsertDataTableIntoExcel(DataTable excelData, string cellRef)
        {
            if (_excelDoc != null && SheetData != null)
            {
                if (excelData.Rows.Count > 0)
                {
                    try
                    {
                        int rowIndex = 1;
                        //row index 
                        GetRowIndexFromCellRefrence(cellRef, out rowIndex);
                        //get the column index
                        uint columnIndex = GetColumnIndexFromColumnName(GetColumnNameFromCellRefrence(cellRef));

                        uint lastRowIndex = (uint)rowIndex;
                        //each datacell  value is inserted into the  from the specified row index and column index retrieved from cell ref
                        for (int row = 0; row < excelData.Rows.Count; row++)
                        {
                            //returns the row if exists else adds a new row and returns
                            Row dataRow = GetRow(lastRowIndex, true);
                            int colIndex = (int)columnIndex;
                            for (int col = 0; col < excelData.Columns.Count; col++)
                            { // gets the cell object,if exists else creates a cell objects and returns the object from the data row 
                                Cell cell = GetCell(dataRow, colIndex, lastRowIndex);
                                // inserts the datacell value as string in the cells
                                CellValueInsert(cell, excelData.Rows[row][col]);
                                colIndex++;
                            }
                            lastRowIndex++;
                        }
                    }
                    catch (OpenXmlPackageException ex)
                    {
                        throw ex;
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                }
                else
                {
                    OpenXmlPackageException openEx = new OpenXmlPackageException("No data from datatable");
                    throw openEx;
                }
            }
            else
            {
                OpenXmlPackageException openEx = new OpenXmlPackageException("Workbook not found");
                throw openEx;
            }
        }

        /// <summary>
        /// used to insert large data into Excel 
        /// </summary>
        /// <param name="excelData">DataTable param</param>
        /// <param name="startRowIndex">If not specified writes from 1st Row</param>
        public void InsertBulkDataTableIntoExcel(DataTable excelData, int startRowIndex = 1)
        {
            int row, col;
            int rowCount = 0;
            uint rowIndex = (uint)startRowIndex;

            WorkbookPart workbookPart = _excelDoc.WorkbookPart;
            WorksheetPart replacementPart = workbookPart.AddNewPart<WorksheetPart>();
            string origninalSheetId = workbookPart.GetIdOfPart(WorkSheetPart);
            string replacementPartId = workbookPart.GetIdOfPart(replacementPart);

            OpenXmlReader reader = OpenXmlReader.Create(WorkSheetPart);
            OpenXmlWriter writer = OpenXmlWriter.Create(replacementPart);

            while (reader.Read())
            {
                if (reader.ElementType == typeof(SheetData))
                {
                    if (reader.IsEndElement)
                        continue;
                    writer.WriteStartElement(new SheetData());
                    // read first row from template and copy into the new sheet
                    reader.Read();
                    while (!(reader.ElementType == typeof(SheetData) && reader.IsEndElement))
                    {
                        if (rowCount < rowIndex)
                        {
                            if (reader.IsStartElement)
                            {
                                writer.WriteStartElement(reader);
                                // this bit is needed to get cell values
                                if (reader.ElementType.IsSubclassOf(typeof(OpenXmlLeafTextElement)))
                                {
                                    writer.WriteString(reader.GetText());
                                }
                            }
                            else if (reader.IsEndElement)
                            {
                                writer.WriteEndElement();
                            }
                        }
                        if (reader.ElementType == typeof(Row) && reader.IsEndElement)
                            rowCount++;

                        reader.Read();
                    }

                    for (row = 0; row < excelData.Rows.Count; row++)
                    {

                        writer.WriteStartElement(new Row() { RowIndex = rowIndex });
                        for (col = 0; col < excelData.Columns.Count; col++)
                        {
                           // Cell cell = CellValueInsert(excelData.Rows[row][col].ToString(), excelData.Rows[row][col].GetType().ToString());
                            //cell.CellReference = GetColumnName(col + 1) + rowIndex;
                          //  writer.WriteElement(cell);
                        }
                        writer.WriteEndElement();
                        rowIndex++;
                    }
                    writer.WriteEndElement();
                }
                else
                {
                    if (reader.IsStartElement)
                    {
                        writer.WriteStartElement(reader);
                        // this bit is needed to get formulae and that kind of thing
                        if (reader.ElementType.IsSubclassOf(typeof(OpenXmlLeafTextElement)))
                        {
                            writer.WriteString(reader.GetText());
                        }
                    }
                    else if (reader.IsEndElement)
                    {
                        writer.WriteEndElement();
                    }
                }
            }
            reader.Close();
            writer.Close();
            Sheet sheet = workbookPart.Workbook.Descendants<Sheet>().Where(s => s.Id.Value.Equals(origninalSheetId)).First();
            sheet.Id.Value = replacementPartId;
            workbookPart.DeletePart(WorkSheetPart);
            workbookPart.Workbook.Save();
        }

        /// <summary>
        /// Inserts Data from ArrayList into excel
        /// </summary>
        /// <param name="excelData">List of Arrays</param>
        public void InsertArrayListIntoExcel(ArrayList excelData, int rowIndex = 1)
        {
            if (_excelDoc != null && excelData.Count > 0)
            {
                if (SheetData != null)
                {
                    try
                    {
                        //row index where the insertion starts
                        uint lastRowIndex = (uint)rowIndex;

                        foreach (Array tempRow in excelData)
                        {
                            //column 1 where insertion of data starts
                            int columnIndex = 1;
                            //returns the row object as specified in row index if exists, else adds a new row and returns
                            Row dataRow = GetRow(lastRowIndex, true);
                            foreach (object item in tempRow)
                            {
                                //returns cell object,if exists else creates a cell objects and returns the object from the data row 
                                Cell cell = GetCell(dataRow, columnIndex, lastRowIndex);
                                //inserts the value into excel cell
                                CellValueInsert(cell, item);
                                columnIndex++;
                            }
                            lastRowIndex++;
                        }
                    }
                    catch (OpenXmlPackageException ex)
                    {
                        throw ex;
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                }
                else
                {
                    OpenXmlPackageException openEx = new OpenXmlPackageException("No data");
                    throw openEx;
                }
            }
            else
            {
                OpenXmlPackageException openEx = new OpenXmlPackageException("Workbook not found");
                throw openEx;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="excelData">List of Arrays</param>
        public void InsertArrayListIntoExcel(ArrayList excelData, string cellRef)
        {
            if (_excelDoc != null && excelData.Count > 0)
            {
                if (SheetData != null)
                {
                    try
                    {
                        int rowIndex = 1;
                        GetRowIndexFromCellRefrence(cellRef, out rowIndex);
                        //get the column and row index from where insertion of data should start
                        uint columnIndex = GetColumnIndexFromColumnName(GetColumnNameFromCellRefrence(cellRef));
                        uint lastRowIndex = (uint)rowIndex;

                        foreach (Array tempRow in excelData)
                        {
                            int colIndex = (int)columnIndex;

                            //returns the row object as specified in row index if exists, else adds a new row and returns
                            Row dataRow = GetRow(lastRowIndex, true);
                            foreach (object item in tempRow)
                            {
                                //returns cell object,if exists else creates a cell objects and returns the object from the data row 
                                Cell cell = GetCell(dataRow, colIndex, lastRowIndex);
                                //inserts the value into excel cell
                                CellValueInsert(cell, item);
                                colIndex++;
                            }

                            lastRowIndex++;
                        }
                    }
                    catch (OpenXmlPackageException ex)
                    {
                        throw ex;
                    }
                }
                else
                {
                    OpenXmlPackageException openEx = new OpenXmlPackageException("No data");
                    throw openEx;
                }
            }
            else
            {
                OpenXmlPackageException openEx = new OpenXmlPackageException("Workbook not found");
                throw openEx;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="excelData">2 dimenssional object Array Param</param>
        public void InsertTwoDimenssionalArrayIntoExcel(object[,] excelData, int rowCount, int colCount, int rowIndex = 1)
        {
            if (_excelDoc != null && excelData.Length > 0)
            {
                if (SheetData != null)
                {
                    try
                    {
                        //insertion starts from the row specified or by default 1st row
                        uint lastRowIndex = (uint)rowIndex;

                        for (int i = 0; i < rowCount; i++)
                        {
                            //insertion starts from 1st column 
                            int columnIndex = 1;

                            //returns the row object as specified in row index if exists, else adds a new row and returns
                            Row dataRow = GetRow(lastRowIndex, true);

                            for (int j = 0; j < colCount; j++)
                            {
                                //returns cell object,if exists else creates a cell objects and returns the object from the data row 
                                Cell cell = GetCell(dataRow, columnIndex, lastRowIndex);
                                //inserts the value into excel cell
                                CellValueInsert(cell, excelData[i, j]);
                                columnIndex++;
                            }
                            lastRowIndex++;
                        }
                    }
                    catch (OpenXmlPackageException ex)
                    {
                        throw ex;
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                }
                else
                {
                    OpenXmlPackageException openEx = new OpenXmlPackageException("No data");
                    throw openEx;
                }
            }
            else
            {
                OpenXmlPackageException openEx = new OpenXmlPackageException("Workbook not found");
                throw openEx;
            }
        }

        /// <summary>
        /// inserts a 2 d array into excel from the given address as parameter.
        /// </summary>
        /// <param name="excelData">2 dimenssional object Array Param</param>
        /// <param name="rowCount">depth of array</param>
        /// <param name="colCount">width of array</param>
        /// <param name="cellRef">cell adrress where insertion starts</param>
        public void InsertTwoDimenssionalArrayIntoExcel(object[,] excelData, int rowCount, int colCount, string cellRef)
        {
            if (_excelDoc != null && excelData.Length > 0)
            {
                if (SheetData != null)
                {
                    try
                    {
                        //insertion starts from the row specified from cell reference 
                        int rowIndex = 1;
                        GetRowIndexFromCellRefrence(cellRef, out rowIndex);
                        //insertion starts from the column specified from cell reference 
                        uint columnIndex = GetColumnIndexFromColumnName(GetColumnNameFromCellRefrence(cellRef));

                        uint lastRowIndex = (uint)rowIndex;

                        for (int i = 0; i < rowCount; i++)
                        {
                            int colIndex = (int)columnIndex;
                            //returns the row object as specified in row index if exists, else adds a new row and returns
                            Row dataRow = GetRow(lastRowIndex, true);
                            for (int j = 0; j < colCount; j++)
                            { //returns cell object,if exists else creates a cell objects and returns the object from the data row 
                                Cell cell = GetCell(dataRow, colIndex, lastRowIndex);
                                //inserts the value into excel cell
                                CellValueInsert(cell, excelData[i, j]);
                                colIndex++;
                            }
                            lastRowIndex++;
                        }
                    }
                    catch (OpenXmlPackageException ex)
                    {
                        throw ex;
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                }
                else
                {
                    OpenXmlPackageException openEx = new OpenXmlPackageException("No data");
                    throw openEx;
                }
            }
            else
            {
                OpenXmlPackageException openEx = new OpenXmlPackageException("Workbook not found");
                throw openEx;
            }
        }

        /// <summary>       
        /// Unhides/Hides a sheet
        /// </summary>
        /// <param name="worksheetName">Name of the WorkSheet(Case sensitive)</param>
        /// <param name="isHide">True to Hide, False to Visible</param>
        public void HideorUnhideSheet(string worksheetName, bool isHide)
        {
            if (string.IsNullOrEmpty(worksheetName))
            {
                throw new OpenXmlPackageException("Missing SheetName");
            }

            // get all sheets from workbook
            IEnumerable<Sheet> sheets = _excelDoc.WorkbookPart.Workbook.Descendants<Sheet>();

            foreach (Sheet item in sheets)
            {
                WorkSheetPart = (WorksheetPart)_excelDoc.WorkbookPart.GetPartById(item.Id);

                // for each sheet in workbook
                if (item.Name == worksheetName)
                {
                    if (isHide)
                    {
                        // hide the sheet by using state parameter
                        item.State = SheetStateValues.Hidden;

                    }
                    else
                    { // Unhide the sheet by using state parameter
                        item.State = SheetStateValues.Visible;

                    }
                    //save the workbook
                    _excelDoc.WorkbookPart.Workbook.Save();
                    return;

                }


            }


            throw new OpenXmlPackageException("Sheet not found");
        }

        /// <summary>
        /// Hides Rows from start index to end index
        /// </summary>
        /// <param name="startRowIndex">start row index/number</param>
        /// <param name="endRowIndex">end row index/number</param>
        public void HideRows(int startRowIndex, int endRowIndex)
        {
            for (int i = startRowIndex; i <= endRowIndex; i++)
            {
                // get the row as specified by the row index if exists or creates a new row 
                Row dataRow = GetRow((uint)i, true);
                //Hidden attribute is made true
                dataRow.Hidden = true;
            }
        }

        /// <summary>
        /// Hides a particular Row 
        /// </summary>
        /// <param name="rowIndex"> row number/index</param>
        public void HideRow(int rowIndex)
        {
            Row dataRow = GetRow((uint)rowIndex, true);
            dataRow.Hidden = true;
        }

        /// <summary>
        /// get the specified cell value as string
        /// </summary>
        /// <param name="cellReference">Cell reference or Address like "A1","J20"</param>
        /// <returns>string value of cell</returns>
        public string Cell(string cellReference)
        {

            if (SheetData == null)
            {
                OpenXmlPackageException openEx = new OpenXmlPackageException("No Sheet Selected");
                throw openEx;
            }

            string cellValue = null;
            int rowIndex = 0;
            Row dataRow = null;
            Cell cell = null;
            //check if the row exists
            if (GetRowIndexFromCellRefrence(cellReference, out rowIndex))
            {
                //get the row object
                dataRow = GetRow((uint)rowIndex);
                if (dataRow == null)
                    return null;
                //get the cell object from row
                cell = dataRow.Elements<Cell>().Where(c => string.Compare(c.CellReference.Value, cellReference, true) == 0).FirstOrDefault();
                if (cell == null)
                    return null;
                // check if the cell value is string and is stored in sharedstringtable
                if (cell.DataType != null && (cell.DataType.Value == CellValues.SharedString))
                {
                    int id;
                    // get the index of the shared string table where the cell value is stored using int.TryParse as its stored in uint
                    if (int.TryParse(cell.CellValue.Text, out id))
                    {
                        //gets the value from shared string table
                        cellValue = _excelDoc.WorkbookPart.SharedStringTablePart.SharedStringTable.Elements<SharedStringItem>().ElementAt(id).InnerText;
                    }
                }
                else
                {
                    //if its number or any other format the cellvalue can be directly fetched
                    if (cell.CellValue != null)
                        cellValue = cell.CellValue.Text;
                }
            }
            return cellValue;
        }

        /// <summary>
        /// Used to apply style to a cell
        /// </summary>
        /// <param name="cellName">Cell Name: Ex: A1 (row 1 column 1) </param>
        /// <param name="font">DocumentFormat.OpenXml.Spreadsheet.Font object</param>
        /// <param name="fill">DocumentFormat.OpenXml.Spreadsheet.Fill object</param>
        /// <param name="border">DocumentFormat.OpenXml.Spreadsheet.Border object</param>
        /// <param name="numberFormatId">FormatId, Standard ECMA-376 Office Open XML File Formats, Ex: '14' for 'mm-dd-yy' format </param>
        public void ApplyStyleToCell(string SheetName, string cellReference, Font font, Fill fill, Border border, UInt32Value numberFormatId, Alignment alignment)
        {
            UInt32Value formatStyleIndex;

            // get the sheetdata
            if (string.IsNullOrEmpty(SheetName))
            {
                throw new OpenXmlPackageException("Missing SheetName");
            }
            IEnumerable<Sheet> sheet = _excelDoc.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>().Where(s => s.Name == SheetName);
            if (sheet.Count() == 0)
            {
                OpenXmlPackageException openEx = new OpenXmlPackageException("WorkSheets not found");
                throw openEx;
            }

            string relationshipId = sheet.FirstOrDefault().Id.Value;
            WorkSheetPart = (WorksheetPart)_excelDoc.WorkbookPart.GetPartById(relationshipId);
            if (WorkSheetPart == null)
            {
                OpenXmlPackageException openEx = new OpenXmlPackageException("WorkSheets not found");
                throw openEx;
            }
            SheetData = WorkSheetPart.Worksheet.Elements<SheetData>().FirstOrDefault();

            // get the specified cell object if exists or a new one is created 
            Cell cellName = GetCell(cellReference);

            //  style index , returns the 0 based index value of the cellformat from cellformatlist if exists or creates a new cell format
            formatStyleIndex = CreateCellFormat(CreateFont(font), CreateFill(fill), CreateBorder(border), numberFormatId, alignment);

            if (cellName != null && formatStyleIndex.HasValue)
            {
                //assign the formatstyle index to style index of cell
                cellName.StyleIndex = formatStyleIndex;
                //save worksheet
                WorkSheetPart.Worksheet.Save();
            }
            else
            {
                string message = "Style index is not proper";
                if (cellName == null)
                    message = "Cell Not found";



                OpenXmlPackageException openEx = new OpenXmlPackageException(message);
                throw openEx;

            }
        }

        /// <summary>
        /// Used to apply style to a Row
        /// </summary>
        /// <param name="rowIndex">Row Number: Ex: 1 (row 1) </param>
        /// <param name="font">DocumentFormat.OpenXml.Spreadsheet.Font object</param>
        /// <param name="fill">DocumentFormat.OpenXml.Spreadsheet.Fill object</param>
        /// <param name="border">DocumentFormat.OpenXml.Spreadsheet.Border object</param>
        /// <param name="numberFormatId">FormatId, Standard ECMA-376 Office Open XML File Formats, Ex: '14' for 'mm-dd-yy' format </param>
        public void ApplyStyleToRow(int rowIndex, Font font, Fill fill, Border border, UInt32Value numberFormatId, Alignment alignment)
        {
            try
            {
                UInt32Value formatStyleIndex;
                //  style index , returns the 0 based index value of the cellformat from cellformatlist if exists or creates a new cell format
                formatStyleIndex = CreateCellFormat(CreateFont(font), CreateFill(fill), CreateBorder(border), numberFormatId, alignment);
                Row styleRow = GetRow((uint)rowIndex);

                if (styleRow != null && formatStyleIndex.HasValue)
                {
                    // for each cell in a row the style index to formatstyleindex that is retrieved
                    //style is applied to only rows that contain values/ only if cell exists
                    foreach (Cell cell in styleRow)
                    {
                        cell.StyleIndex = formatStyleIndex;
                    }
                }
                else
                {
                    string message = "Style index is not proper";
                    if (styleRow == null)
                        message = "Row Not found";

                    OpenXmlPackageException openEx = new OpenXmlPackageException(message);
                    throw openEx;
                }
            }
            catch (OpenXmlPackageException opx)
            {
                throw opx;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Deletes the worksheet
        /// </summary>
        /// <param name="sheetToDelete">Sheet name that has to be deleted</param>
        public void DeleteAWorkSheet(string sheetToDelete)
        {
            string Sheetid = "";
            //Open the workbook

            WorkbookPart wbPart = WorkBookPart;

            // Get the pivot Table Parts
            IEnumerable<PivotTableCacheDefinitionPart> pvtTableCacheParts = wbPart.PivotTableCacheDefinitionParts;
            Dictionary<PivotTableCacheDefinitionPart, string> pvtTableCacheDefinationPart = new Dictionary<PivotTableCacheDefinitionPart, string>();
            foreach (PivotTableCacheDefinitionPart Item in pvtTableCacheParts)
            {
                PivotCacheDefinition pvtCacheDef = Item.PivotCacheDefinition;
                //Check if this CacheSource is linked to SheetToDelete
                var pvtCahce = pvtCacheDef.Descendants<CacheSource>().Where(s => s.WorksheetSource.Sheet == sheetToDelete);
                if (pvtCahce.Count() > 0)
                {
                    pvtTableCacheDefinationPart.Add(Item, Item.ToString());
                }
            }
            foreach (var Item in pvtTableCacheDefinationPart)
            {
                wbPart.DeletePart(Item.Key);
            }
            //Get the SheetToDelete from workbook.xml
            Sheet theSheet = wbPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetToDelete).FirstOrDefault();
            if (theSheet == null)
            {
                throw new Exception("Sheet:-" + sheetToDelete + " Doesnt exists");

            }
            //Store the SheetID for the reference
            Sheetid = theSheet.SheetId;

            // Remove the sheet reference from the workbook.
            WorksheetPart worksheetPart = (WorksheetPart)(wbPart.GetPartById(theSheet.Id));
            theSheet.Remove();

            // Delete the worksheet part.
            wbPart.DeletePart(worksheetPart);

            //Get the DefinedNames
            var definedNames = wbPart.Workbook.Descendants<DefinedNames>().FirstOrDefault();
            if (definedNames != null)
            {
                foreach (DefinedName Item in definedNames)
                {
                    // This condition checks to delete only those names which are part of Sheet in question
                    if (Item.Text.Contains(sheetToDelete + "!"))
                        Item.Remove();
                }
            }
            // Get the CalculationChainPart 
            //Note: An instance of this part type contains an ordered set of references to all cells in all worksheets in the 
            //workbook whose value is calculated from any formula

            CalculationChainPart calChainPart;
            calChainPart = wbPart.CalculationChainPart;
            if (calChainPart != null)
            {
                var calChainEntries = calChainPart.CalculationChain.Descendants<CalculationCell>().Where(c => c.SheetId == Sheetid);
                foreach (CalculationCell Item in calChainEntries)
                {
                    Item.Remove();
                }
                if (calChainPart.CalculationChain.Count() == 0)
                {
                    wbPart.DeletePart(calChainPart);
                }
            }

            // Save the workbook.
            wbPart.Workbook.Save();

        }

        /// <summary>
        /// Renames a specified Sheet
        /// </summary>
        /// <param name="OldSheetName">Old sheet Name</param>
        /// <param name="NewSheetName">New Sheet Name</param>
        public void RenameSheet(string OldSheetName, string NewSheetName)
        {
            WorkbookPart wbPart = WorkBookPart;

            // get the specified sheet name from the workbook by name
            Sheet theSheet = wbPart.Workbook.Descendants<Sheet>().Where(s => s.Name == OldSheetName).FirstOrDefault();
            if (theSheet == null)
            {
                throw new Exception("Sheet:-" + OldSheetName + " Doesnt exists");

            }
            Sheets sheets = _excelDoc.WorkbookPart.Workbook.GetFirstChild<Sheets>();

            // get all sheet names to check if  new sheet name already exists in the workbook
            List<string> sheetnames = new List<string>();
            foreach (Sheet s in sheets)
            {
                sheetnames.Add(s.Name);
            }


            if (sheetnames.Contains(NewSheetName))
            {
                throw new OpenXmlPackageException("Sheet Name " + NewSheetName + " already exists");
            }

            // set the new sheet name to the worksheet
            theSheet.Name = NewSheetName;

            wbPart.Workbook.Save();



        }

        /// <summary>
        /// Count of sheets in a workbook
        /// </summary>
        /// <returns>Count of sheets</returns>
        public int CountOfSheets()
        {
            WorkbookPart wbPart = WorkBookPart;
            // get sheet count
            int count = wbPart.Workbook.Descendants<Sheet>().Count();
            if (count == 0)
            {
                throw new Exception("No Sheet is present");
            }
            else
            {
                return count;
            }
        }

        /// <summary>
        /// Number of rows used in a worksheet
        /// </summary>
        /// <param name="sheetName"></param>
        /// <returns>int- row count</returns>
        public int NumberOfRowsUsed(string sheetName)
        {

            IEnumerable<Sheet> sheet = _excelDoc.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>().Where(s => s.Name == sheetName);
            if (sheet.Count() == 0)
            {
                OpenXmlPackageException openEx = new OpenXmlPackageException("WorkSheets not found");
                throw openEx;
            }
            string relationshipId = sheet.FirstOrDefault().Id.Value;
            WorkSheetPart = (WorksheetPart)_excelDoc.WorkbookPart.GetPartById(relationshipId);
            // sheet data is retrieved from worksheet
            SheetData = WorkSheetPart.Worksheet.Elements<SheetData>().FirstOrDefault();
            //count of rows
            int count = SheetData.Elements<Row>().Count();

            return count;
        }

        /// <summary>
        /// Sets a given formula to entire cells in a range
        /// </summary>
        /// <param name="SheetName">Sheet Name</param>
        /// <param name="startCellReference">start cellreference of range</param>
        /// <param name="endCellReference">end cellreference of range</param>
        /// <param name="formula">formuala to be applied</param>
        public void SetFormulaToRange(string SheetName, string startCellReference, string endCellReference, string formula)
        {
            int startrowindex;
            int endrowindex;
            //get the start and end row and column indexes to get all cell references
            int startcolindex = Column(startCellReference, out startrowindex);
            int endcolindex = Column(endCellReference, out endrowindex);

            if (string.IsNullOrEmpty(SheetName))
            {
                throw new OpenXmlPackageException("Missing SheetName");
            }
            IEnumerable<Sheet> sheet = _excelDoc.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>().Where(s => s.Name == SheetName);
            if (sheet.Count() == 0)
            {
                OpenXmlPackageException openEx = new OpenXmlPackageException("WorkSheets not found");
                throw openEx;
            }
            string relationshipId = sheet.FirstOrDefault().Id.Value;
            WorkSheetPart = (WorksheetPart)_excelDoc.WorkbookPart.GetPartById(relationshipId);
            if (WorkSheetPart == null)
            {
                OpenXmlPackageException openEx = new OpenXmlPackageException("WorkSheets not found");
                throw openEx;
            }
            SheetData = WorkSheetPart.Worksheet.Elements<SheetData>().FirstOrDefault();

            // get all cell indexes
            for (int col = startcolindex; col <= endcolindex; col++)
            {
                for (int row = startrowindex; row <= endrowindex; row++)
                {
                    string cellreference = GetColumnName(col) + row.ToString();
                    // get the cell objet if exists to creates a new one
                    Cell cell = GetCell(cellreference);
                    //create a cellformula object
                    CellFormula cellformula = new CellFormula();
                    cellformula.Text = formula;
                    // remove all children 
                    cell.RemoveAllChildren();
                    // append the cellformula object
                    cell.Append(cellformula);

                }

            }

            WorkSheetPart.Worksheet.Save();


        }

        /// <summary>
        /// inserts a new worksheet in the workbook
        /// </summary>
        /// <param name="sheetname">sheet name of the sheet to be added</param>
        public void InsertWorksheet(string sheetname)
        {
           
                // Open the document for editing.

                // Add a blank WorksheetPart.
                WorksheetPart newWorksheetPart = _excelDoc.WorkbookPart.AddNewPart<WorksheetPart>();
                newWorksheetPart.Worksheet = new Worksheet(new SheetData());

                Sheets sheets = _excelDoc.WorkbookPart.Workbook.GetFirstChild<Sheets>();
                string relationshipId = _excelDoc.WorkbookPart.GetIdOfPart(newWorksheetPart);

                List<string> sheetnames = new List<string>();
                foreach (Sheet s in sheets)
                {
                    sheetnames.Add(s.Name);
                }


                if (sheetnames.Contains(sheetname))
                {
                    throw new OpenXmlPackageException("Sheet Name " + sheetname + " already exists");
                }
                // Get a unique ID for the new worksheet.
                uint sheetId = 1;
                if (sheets.Elements<Sheet>().Count() > 0)
                {
                    sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
                }
                 


                // Give the new worksheet a name.
                string sheetName = sheetname;

                // Append the new worksheet and associate it with the workbook.
                Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };

                sheets.Append(sheet);
            


        }

        /// <summary>
        /// Merges the cells
        /// </summary>
        /// <param name="SheetName">Sheet Name</param>
        /// <param name="startCellReference">start cell reference/address as string</param>
        /// <param name="endCellReference">End cell reference/address as string</param>
        public void MergeCells(string SheetName, string startCellReference, string endCellReference)
        {
            if (string.IsNullOrEmpty(SheetName))
            {
                throw new OpenXmlPackageException("Missing SheetName");
            }
            IEnumerable<Sheet> sheet = _excelDoc.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>().Where(s => s.Name == SheetName);
            if (sheet.Count() == 0)
            {
                OpenXmlPackageException openEx = new OpenXmlPackageException("WorkSheets not found");
                throw openEx;
            }

            string relationshipId = sheet.FirstOrDefault().Id.Value;
            WorkSheetPart = (WorksheetPart)_excelDoc.WorkbookPart.GetPartById(relationshipId);
            if (WorkSheetPart == null)
            {
                OpenXmlPackageException openEx = new OpenXmlPackageException("WorkSheets not found");
                throw openEx;
            }
            SheetData = WorkSheetPart.Worksheet.Elements<SheetData>().FirstOrDefault();
            Worksheet worksheet = WorkSheetPart.Worksheet;

            //if cell doesnt exists.. the cell will be created
            GetCell(startCellReference);
            GetCell(endCellReference);

            MergeCells mergeCells;

            if (worksheet.Elements<MergeCells>().Count() > 0)
                mergeCells = worksheet.Elements<MergeCells>().First();
            else
            {
                mergeCells = new MergeCells();

                // Insert a MergeCells object into the specified position.
                if (worksheet.Elements<CustomSheetView>().Count() > 0)
                    worksheet.InsertAfter(mergeCells, worksheet.Elements<CustomSheetView>().First());
                else
                    worksheet.InsertAfter(mergeCells, worksheet.Elements<SheetData>().First());
            }

            List<string> lstofCells = new List<string>();
            foreach (MergeCell mer in mergeCells)
            {

                string[] cellRef = mer.Reference.Value.Split(':');

                lstofCells.AddRange(GetAllCellsFromRange(cellRef[0], cellRef[1]));

            }

            if (lstofCells.Contains(startCellReference) || lstofCells.Contains(endCellReference))
            {
                throw new OpenXmlPackageException("Cell already part of another merge");

            }
            // Create the merged cell and append it to the MergeCells collection.
            MergeCell mergeCell = new MergeCell()
            {
                Reference =
                    new StringValue(startCellReference + ":" + endCellReference)
            };
            mergeCells.Append(mergeCell);
            worksheet.Save();
        }
        /// <summary>
        /// Lock/Protect Sheet
        /// </summary>
        /// <param name="SheetName"> sheet name </param>
        /// <param name="password"> password</param>
        public void LockSheet(string SheetName, string password)
        {
            if (string.IsNullOrEmpty(SheetName))
            {
                throw new OpenXmlPackageException("Missing SheetName");
            }
            // get the sheet by sheet name
            IEnumerable<Sheet> sheet = _excelDoc.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>().Where(s => s.Name == SheetName);
            if (sheet.Count() == 0)
            {
                OpenXmlPackageException openEx = new OpenXmlPackageException("WorkSheets not found");
                throw openEx;
            }

            string relationshipId = sheet.FirstOrDefault().Id.Value;
            WorkSheetPart = (WorksheetPart)_excelDoc.WorkbookPart.GetPartById(relationshipId);
            if (WorkSheetPart == null)
            {
                OpenXmlPackageException openEx = new OpenXmlPackageException("WorkSheets not found");
                throw openEx;
            }
            PageMargins pageMargins1 = WorkSheetPart.Worksheet.GetFirstChild<PageMargins>();

            //Create sheetprotection class along with password and other properties
            SheetProtection sheetProtection1 = new SheetProtection() { Password = GetSheetPassword(password), Sheet = true, Objects = true, Scenarios = true };

            // insert the sheetprotection class before the pagemargin object
            
                DataValidations dv = WorkSheetPart.Worksheet.GetFirstChild<DataValidations>();
            if(dv!=null)
            WorkSheetPart.Worksheet.InsertBefore(sheetProtection1, dv);
            else
                WorkSheetPart.Worksheet.InsertBefore(sheetProtection1, pageMargins1);
            
            WorkSheetPart.Worksheet.Save();




        }

        /// <summary>
        /// unlocks the sheet with  given password 
        /// </summary>
        /// <param name="SheetName">sheet name </param>
        /// <param name="password"> password</param>
        public void UnLockSheet(string SheetName, string password)
        {
            if (string.IsNullOrEmpty(SheetName))
            {
                throw new OpenXmlPackageException("Missing SheetName");
            }
            IEnumerable<Sheet> sheet = _excelDoc.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>().Where(s => s.Name == SheetName);
            if (sheet.Count() == 0)
            {
                OpenXmlPackageException openEx = new OpenXmlPackageException("WorkSheets not found");
                throw openEx;
            }

            string relationshipId = sheet.FirstOrDefault().Id.Value;
            WorkSheetPart = (WorksheetPart)_excelDoc.WorkbookPart.GetPartById(relationshipId);
            if (WorkSheetPart == null)
            {
                OpenXmlPackageException openEx = new OpenXmlPackageException("WorkSheets not found");
                throw openEx;
            }
            Worksheet worksheet = WorkSheetPart.Worksheet;
            //check if there is any sheetprotection present in a sheet
            if (worksheet.Descendants<SheetProtection>().Count<SheetProtection>() > 0)
            {
                // validate the given password and password in the sheet
                string strChkPwd = worksheet.Descendants<SheetProtection>().First<SheetProtection>().Password.Value;
                //get the hashed password and compare
                if (GetSheetPassword(password) == strChkPwd)
                {
                    // remove sheetproptection child from worksheet tag
                    worksheet.RemoveAllChildren<SheetProtection>();
                    worksheet.Save();
                }
            }

        }

        /// <summary>
        /// Apply formulae to cell
        /// </summary>
        /// <param name="SheetName"> Sheet Name</param>
        /// <param name="CellReference">cell reference /address</param>
        /// <param name="formula">formula to be applied</param>
        public void SetFormulaToCell(string SheetName, string CellReference, string formula)
        {


            if (string.IsNullOrEmpty(SheetName))
            {
                throw new OpenXmlPackageException("Missing SheetName");
            }
            IEnumerable<Sheet> sheet = _excelDoc.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>().Where(s => s.Name == SheetName);
            if (sheet.Count() == 0)
            {
                OpenXmlPackageException openEx = new OpenXmlPackageException("WorkSheets not found");
                throw openEx;
            }

            string relationshipId = sheet.FirstOrDefault().Id.Value;
            WorkSheetPart = (WorksheetPart)_excelDoc.WorkbookPart.GetPartById(relationshipId);
            if (WorkSheetPart == null)
            {
                OpenXmlPackageException openEx = new OpenXmlPackageException("WorkSheets not found");
                throw openEx;
            }
            SheetData = WorkSheetPart.Worksheet.Elements<SheetData>().FirstOrDefault();

            // get the cell if exists else insert a new cell at given reference
            Cell cell = GetCell(CellReference);
            // create a new cellformula and append this to cell object
            CellFormula cellformula = new CellFormula();
            cellformula.Text = formula;

            cell.RemoveAllChildren();
            cell.Append(cellformula);
            WorkSheetPart.Worksheet.Save();


        }

        /// <summary>
        /// Hide the specified column
        /// </summary>
        /// <param name="worksheetName"> Work sheet name</param>
        /// <param name="columnno">column number</param>
        public void HideColumn(string worksheetName, int columnno)
        {

            if (string.IsNullOrEmpty(worksheetName))
            {
                throw new OpenXmlPackageException("Missing SheetName");
            }

            IEnumerable<Sheet> sheets = _excelDoc.WorkbookPart.Workbook.Descendants<Sheet>();

            foreach (Sheet item in sheets)
            {
                WorkSheetPart = (WorksheetPart)_excelDoc.WorkbookPart.GetPartById(item.Id);
                if (item.Name == worksheetName)
                {
                    SheetData = WorkSheetPart.Worksheet.Elements<SheetData>().FirstOrDefault();
                    Columns columns = WorkSheetPart.Worksheet.Elements<Columns>().FirstOrDefault();
                    Column col = WorkSheetPart.Worksheet.Elements<Column>().Where(r => r.Max == columnno).FirstOrDefault();
                    if (col == null)
                    {
                        if (columns == null)
                        {
                            columns = new Columns();
                        }
                        // Min & Max refer to the 1-indexed column ordinal 
                        Column currentcol = new Column() { Min = (UInt32)columnno, Max = (UInt32)columnno, Width = 0D, CustomWidth = true, Hidden = true };
                        // ... repeat for each column  
                        columns.Append(currentcol);

                        if (WorkSheetPart.Worksheet.Elements<Columns>().FirstOrDefault() == null)
                        {
                            SheetFormatProperties sheetformatproperties = WorkSheetPart.Worksheet.GetFirstChild<SheetFormatProperties>();
                            
                            // insert after sheet format properties
                            WorkSheetPart.Worksheet.InsertAfter(columns, sheetformatproperties);
                        }
                    }
                    else { col.Hidden = true; }

                    WorkSheetPart.Worksheet.Save();


                }
            }
        }

        /// <summary>
        /// update the Pivot range and referesh the range
        /// </summary>
        /// <param name="worksheetName">worksheet name</param>
        /// <param name="PivotName">pivot table name</param>
        /// <param name="Range">Range to be updated</param>
        public void PivotTableUpdateRange(string worksheetName, string PivotName, string Range)
        {
            // Open the document for editing.

            IEnumerable<Sheet> sheets = _excelDoc.WorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == worksheetName);
            if (sheets.Count() == 0)
            {
                // The specified worksheet does not exist.
                return;
            }
            WorksheetPart worksheetPart = (WorksheetPart)_excelDoc.WorkbookPart.GetPartById(sheets.First().Id);

            // get pivot table parts from worksheetpart
            IEnumerable<PivotTablePart> pvparts = worksheetPart.PivotTableParts;
            bool foundpivot = false;
            PivotTablePart pvtable = null;
            foreach (PivotTablePart pv in pvparts)
            {
                // get pivot table by name
                if (pv.PivotTableDefinition.Name == PivotName)
                {

                    pvtable = pv;
                    foundpivot = true;
                    break;
                }

            }

            if (foundpivot == true)
            {
                // change the reference source value
                (((pvtable.PivotTableCacheDefinitionPart.PivotCacheDefinition).CacheSource).WorksheetSource).Reference.Value = Range;
                //referesh on load
                (pvtable.PivotTableCacheDefinitionPart.PivotCacheDefinition).RefreshOnLoad = true;
            }


            worksheetPart.Worksheet.Save();







        }

        /// <summary>
        /// Apply Style or formatting to all cells of a range
        /// </summary>
        /// <param name="SheetName">sheet name</param>
        /// <param name="startCellReference">start cell reference of range</param>
        /// <param name="endCellReference">end cell reference of range</param>
        /// <param name="font">DocumentFormat.OpenXml.Spreadsheet.Font object</param>
        /// <param name="fill">DocumentFormat.OpenXml.Spreadsheet.Fill object</param>
        /// <param name="border">DocumentFormat.OpenXml.Spreadsheet.Border object</param>
        /// <param name="numberFormatId">FormatId, Standard ECMA-376 Office Open XML File Formats, Ex: '14' for 'mm-dd-yy' format </param>
        /// <param name="alignment">DocumentFormat.OpenXml.Spreadsheet.Alignment object</param>
        public void ApplyStyleToRange(string SheetName, string startCellReference, string endCellReference, Font font, Fill fill, Border border, UInt32Value numberFormatId, Alignment alignment)
        {
            // gets all the cells in the given range.
            List<string> cells = GetAllCellsFromRange(startCellReference, endCellReference);
            UInt32Value formatStyleIndex;
            if (string.IsNullOrEmpty(SheetName))
            {
                throw new OpenXmlPackageException("Missing SheetName");
            }
            IEnumerable<Sheet> sheet = _excelDoc.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>().Where(s => s.Name == SheetName);
            if (sheet.Count() == 0)
            {
                OpenXmlPackageException openEx = new OpenXmlPackageException("WorkSheets not found");
                throw openEx;
            }

            string relationshipId = sheet.FirstOrDefault().Id.Value;
            WorkSheetPart = (WorksheetPart)_excelDoc.WorkbookPart.GetPartById(relationshipId);
            if (WorkSheetPart == null)
            {
                OpenXmlPackageException openEx = new OpenXmlPackageException("WorkSheets not found");
                throw openEx;
            }
            SheetData = WorkSheetPart.Worksheet.Elements<SheetData>().FirstOrDefault();
            //  style index , returns the 0 based index value of the cellformat from cellformatlist if exists or creates a new cell format
            formatStyleIndex = CreateCellFormat(CreateFont(font), CreateFill(fill), CreateBorder(border), numberFormatId, alignment);

            //apply the style index to all cells in a range
            foreach (string cell in cells)
            {
                Cell cellName = GetCell(cell);

                if (cellName != null && formatStyleIndex.HasValue)
                {
                    cellName.StyleIndex = formatStyleIndex;

                }
                else
                {
                    string message = "Style index is not proper";
                    if (cellName == null)
                        message = "Cell Not found";



                    OpenXmlPackageException openEx = new OpenXmlPackageException(message);
                    throw openEx;

                }
            }

            WorkSheetPart.Worksheet.Save();

        }

        /// <summary>
        /// Applies style to all cells in the range and outerborder to range
        /// </summary>
        /// <param name="SheetName"></param>
        /// <param name="startCellReference"></param>
        /// <param name="endCellReference"></param>
        /// <param name="font">DocumentFormat.OpenXml.Spreadsheet.Font object</param>
        /// <param name="fill">DocumentFormat.OpenXml.Spreadsheet.Fill object</param>
        /// <param name="borderStyle">DocumentFormat.OpenXml.Spreadsheet.BorderStyleValues like thick thin dashed</param>
        /// <param name="numberFormatId">FormatId, Standard ECMA-376 Office Open XML File Formats, Ex: '14' for 'mm-dd-yy' format </param>
        /// <param name="alignment">DocumentFormat.OpenXml.Spreadsheet.Alignment object</param>
        public void ApplyStyleAndBorderToRange(string SheetName, string startCellReference, string endCellReference, Font font, Fill fill, BorderStyleValues borderStyle, UInt32Value numberFormatId, Alignment alignment)
        {
            if (string.IsNullOrEmpty(SheetName))
            {
                throw new OpenXmlPackageException("Missing SheetName");
            }
            IEnumerable<Sheet> sheet = _excelDoc.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>().Where(s => s.Name == SheetName);
            if (sheet.Count() == 0)
            {
                OpenXmlPackageException openEx = new OpenXmlPackageException("WorkSheets not found");
                throw openEx;
            }

            string relationshipId = sheet.FirstOrDefault().Id.Value;
            WorkSheetPart = (WorksheetPart)_excelDoc.WorkbookPart.GetPartById(relationshipId);
            if (WorkSheetPart == null)
            {
                OpenXmlPackageException openEx = new OpenXmlPackageException("WorkSheets not found");
                throw openEx;
            }

            SheetData = WorkSheetPart.Worksheet.Elements<SheetData>().FirstOrDefault();


            UInt32Value formatStyleIndexLeftTop = null;
            UInt32Value formatStyleIndexTop = null;
            UInt32Value formatStyleIndexRightTop = null;
            UInt32Value formatStyleIndexRightBottom = null;
            UInt32Value formatStyleIndexbottom = null;
            UInt32Value formatStyleIndexLeftBottom = null;
            UInt32Value formatStyleIndexLeft = null;
            UInt32Value formatStyleIndexRight = null;
            UInt32Value formatStyleIndex = null;



            UInt32Value formatStyleIndexleftright = null;
            UInt32Value formatStyleIndextopbottom = null;

            UInt32Value formatStyleIndexLeftTopBottom = null;
            UInt32Value formatStyleIndexRightTopBottom = null;

            UInt32Value formatStyleIndexleftrightTop = null;
            UInt32Value formatStyleIndexleftrightBottom = null;


            int startrowindex;
            int endrowindex;
            int startcolindex = Column(startCellReference, out startrowindex);
            int endcolindex = Column(endCellReference, out endrowindex);


            Border border1 = new Border();
            LeftBorder leftBorder1 = new LeftBorder();
            RightBorder rightBorder1 = new RightBorder();
            TopBorder topBorder1 = new TopBorder();
            BottomBorder bottomBorder1 = new BottomBorder();
            DiagonalBorder diagonalBorder1 = new DiagonalBorder();
            UInt32Value formatStyleIndexWithoutBorder = CreateCellFormat(CreateFont(font), CreateFill(fill), CreateBorder(border1), numberFormatId, alignment);
            formatStyleIndex = formatStyleIndexWithoutBorder;
            if (borderStyle != BorderStyleValues.None)
            {
                LeftBorder lb = new LeftBorder(new Color() { Indexed = (UInt32Value)64U }) { Style = borderStyle };
                TopBorder tb = new TopBorder(new Color() { Indexed = (UInt32Value)64U }) { Style = borderStyle };
                RightBorder rb = new RightBorder(new Color() { Indexed = (UInt32Value)64U }) { Style = borderStyle };
                BottomBorder bb = new BottomBorder(new Color() { Indexed = (UInt32Value)64U }) { Style = borderStyle };



                border1.Append(leftBorder1);
                border1.Append(rightBorder1);
                border1.Append(topBorder1);
                border1.Append(bottomBorder1);
                border1.Append(diagonalBorder1);



                Border LeftTopBorder = new Border();
                LeftTopBorder.Append((LeftBorder)lb.Clone());
                LeftTopBorder.Append((TopBorder)tb.Clone());

                Border TopBorder = new Border();
                TopBorder.Append((TopBorder)tb.Clone());

                Border RightTopBorder = new Border();
                RightTopBorder.Append((RightBorder)rb.Clone());
                RightTopBorder.Append((TopBorder)tb.Clone());

                Border LeftBorder = new Border();
                LeftBorder.Append((LeftBorder)lb.Clone());

                Border RightBorder = new Border();
                RightBorder.Append((RightBorder)rb.Clone());

                Border BottomBorder = new Border();
                BottomBorder.Append((BottomBorder)bb.Clone());

                Border LeftBottomBorder = new Border();
                LeftBottomBorder.Append((LeftBorder)lb.Clone());
                LeftBottomBorder.Append((BottomBorder)bb.Clone());

                Border RightBottomBorder = new Border();
                RightBottomBorder.Append((RightBorder)rb.Clone());
                RightBottomBorder.Append((BottomBorder)bb.Clone());



                //single row cases
                Border TopBottomBorder = new Border();
                TopBottomBorder.Append((TopBorder)tb.Clone());
                TopBottomBorder.Append((BottomBorder)bb.Clone());


                Border LeftTopBottomBorder = new Border();
                LeftTopBottomBorder.Append((TopBorder)tb.Clone());
                LeftTopBottomBorder.Append((BottomBorder)bb.Clone());
                LeftTopBottomBorder.Append((LeftBorder)lb.Clone());

                Border RightTopBottomBorder = new Border();
                RightTopBottomBorder.Append((TopBorder)tb.Clone());
                RightTopBottomBorder.Append((BottomBorder)bb.Clone());
                RightTopBottomBorder.Append((RightBorder)rb.Clone());


                //single column cases
                Border leftrightborder = new Border();
                leftrightborder.Append((LeftBorder)lb.Clone());
                leftrightborder.Append((RightBorder)rb.Clone());

                Border leftrightTopborder = new Border();
                leftrightTopborder.Append((TopBorder)tb.Clone());
                leftrightTopborder.Append((LeftBorder)lb.Clone());
                leftrightTopborder.Append((RightBorder)rb.Clone());


                Border leftrightBottomborder = new Border();
                leftrightBottomborder.Append((LeftBorder)lb.Clone());
                leftrightBottomborder.Append((RightBorder)rb.Clone());
                leftrightBottomborder.Append((BottomBorder)bb.Clone());



                formatStyleIndexLeftTop = CreateCellFormat(CreateFont(font), CreateFill(fill), CreateBorder(LeftTopBorder), numberFormatId, alignment);
                formatStyleIndexTop = CreateCellFormat(CreateFont(font), CreateFill(fill), CreateBorder(TopBorder), numberFormatId, alignment);
                formatStyleIndexRightTop = CreateCellFormat(CreateFont(font), CreateFill(fill), CreateBorder(RightTopBorder), numberFormatId, alignment);
                formatStyleIndexRightBottom = CreateCellFormat(CreateFont(font), CreateFill(fill), CreateBorder(RightBottomBorder), numberFormatId, alignment);
                formatStyleIndexbottom = CreateCellFormat(CreateFont(font), CreateFill(fill), CreateBorder(BottomBorder), numberFormatId, alignment);
                formatStyleIndexLeftBottom = CreateCellFormat(CreateFont(font), CreateFill(fill), CreateBorder(LeftBottomBorder), numberFormatId, alignment);
                formatStyleIndexLeft = CreateCellFormat(CreateFont(font), CreateFill(fill), CreateBorder(LeftBorder), numberFormatId, alignment);
                formatStyleIndexRight = CreateCellFormat(CreateFont(font), CreateFill(fill), CreateBorder(RightBorder), numberFormatId, alignment);

                //for single row cases
                formatStyleIndexLeftTopBottom = CreateCellFormat(CreateFont(font), CreateFill(fill), CreateBorder(LeftTopBottomBorder), numberFormatId, alignment);
                formatStyleIndextopbottom = CreateCellFormat(CreateFont(font), CreateFill(fill), CreateBorder(TopBottomBorder), numberFormatId, alignment);
                formatStyleIndexRightTopBottom = CreateCellFormat(CreateFont(font), CreateFill(fill), CreateBorder(RightTopBottomBorder), numberFormatId, alignment);


                //for single column cases
                //formatStyleIndexleftrightTop = CreateCellFormat(CreateFont(font), CreateFill(fill), CreateBorder(leftrightTopborder), numberFormatId, alignment);
                //formatStyleIndexleftright= CreateCellFormat(CreateFont(font), CreateFill(fill), CreateBorder(leftrightborder), numberFormatId, alignment);
                //formatStyleIndexleftrightBottom = CreateCellFormat(CreateFont(font), CreateFill(fill), CreateBorder(leftrightBottomborder), numberFormatId, alignment);



            }


            for (int row = startrowindex; row <= endrowindex; row++)
            {
                for (int col = startcolindex; col <= endcolindex; col++)
                {
                    formatStyleIndex = formatStyleIndexWithoutBorder;
                    if (borderStyle.ToString() != "None")
                    {


                        if (row == startrowindex && col == startcolindex)
                        {
                            formatStyleIndex = formatStyleIndexLeftTop;
                        }

                        if (row == startrowindex && row != endrowindex && col != startcolindex && col != endcolindex)
                        {

                            formatStyleIndex = formatStyleIndexTop;
                        }

                        if (row == startrowindex && col == endcolindex)
                        {


                            formatStyleIndex = formatStyleIndexRightTop;
                        }

                        if (row != startrowindex && row != endrowindex && (col == startcolindex || col == endcolindex))
                        {

                            if (col == startcolindex)
                            {


                                formatStyleIndex = formatStyleIndexLeft;


                            }
                            else
                            {


                                formatStyleIndex = formatStyleIndexRight;
                            }


                        }


                        if (row == endrowindex && col == startcolindex)
                        {



                            formatStyleIndex = formatStyleIndexLeftBottom;


                        }


                        if (row == endrowindex && col != startcolindex && col != endcolindex)
                        {


                            formatStyleIndex = formatStyleIndexbottom;
                        }


                        if (row == endrowindex && col == endcolindex)
                        {
                            formatStyleIndex = formatStyleIndexRightBottom;
                        }


                        #region for single row and single column
                        if ((row == startrowindex && row == endrowindex) && (col != startcolindex && col != endcolindex))
                        {
                            formatStyleIndex = formatStyleIndextopbottom;

                        }

                        if ((row == startrowindex && row == endrowindex) && (col == startcolindex && col != endcolindex))
                        {
                            formatStyleIndex = formatStyleIndexLeftTopBottom;

                        }

                        if ((row == startrowindex && row == endrowindex) && (col != startcolindex && col == endcolindex))
                        {
                            formatStyleIndex = formatStyleIndexRightTopBottom;

                        }

                        if ((row != startrowindex && row != endrowindex) && (col == startcolindex && col == endcolindex))
                        {
                            formatStyleIndex = formatStyleIndexleftright;

                        }


                        if ((row == startrowindex && row != endrowindex) && (col == startcolindex && col == endcolindex))
                        {
                            formatStyleIndex = formatStyleIndexleftrightTop;

                        }
                        if ((row != startrowindex && row == endrowindex) && (col == startcolindex && col == endcolindex))
                        {
                            formatStyleIndex = formatStyleIndexleftrightBottom;

                        }
                        #endregion



                    }


                    string cellreference = GetColumnName(col) + row.ToString();
                    Cell cellName = GetCell(cellreference);
                    if (cellName != null && formatStyleIndex.HasValue)
                    {
                        //if(cellName.CellValue != null)
                        cellName.StyleIndex = formatStyleIndex;

                    }
                    else
                    {
                        string message = "Style index is not proper";
                        if (cellName == null)
                            message = "Cell Not found";

                        OpenXmlPackageException openEx = new OpenXmlPackageException(message);
                        throw openEx;

                    }


                }

            }

            WorkSheetPart.Worksheet.Save();


        }

        /// <summary>
        /// Inserts a dropdown to specified cell
        /// </summary>
        /// <param name="SheetName">Sheet name</param>
        /// <param name="address">cell adrress where drop down to be inserted</param>
        /// <param name="dropdownrange"> range of text to be applied to range </param>
        public void InsertDropdown(string SheetName, string address, string dropdownrange)
        {
            if (string.IsNullOrEmpty(SheetName))
            {
                throw new OpenXmlPackageException("Missing SheetName");
            }
            IEnumerable<Sheet> sheet = _excelDoc.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>().Where(s => s.Name == SheetName);
            if (sheet.Count() == 0)
            {
                OpenXmlPackageException openEx = new OpenXmlPackageException("WorkSheets not found");
                throw openEx;
            }

            string relationshipId = sheet.FirstOrDefault().Id.Value;
            WorkSheetPart = (WorksheetPart)_excelDoc.WorkbookPart.GetPartById(relationshipId);


            if (WorkSheetPart == null)
            {
                OpenXmlPackageException openEx = new OpenXmlPackageException("WorkSheets not found");
                throw openEx;
            }

            DataValidations dataValidations1;
            // get the datavalidations class from worksheetpart
            if (WorkSheetPart.Worksheet.Elements<DataValidations>().Count() > 0)
            {
                dataValidations1 = WorkSheetPart.Worksheet.Elements<DataValidations>().First();

            }
            else
            {
                //add if doesnt exists
                dataValidations1 = new DataValidations() { Count = (UInt32Value)1U };
                WorkSheetPart.Worksheet.InsertAfter(dataValidations1, WorkSheetPart.Worksheet.Elements<SheetData>().First());
            }
            // insert a new List datavalidation
            DataValidation dataValidation1 = new DataValidation() { Type = DataValidationValues.List, AllowBlank = true, ShowInputMessage = true, ShowErrorMessage = true, SequenceOfReferences = new ListValue<StringValue>() { InnerText = address } };

            // assign the range address to a  formula class
            Formula1 formula11 = new Formula1();
            formula11.Text = dropdownrange;

            //append the formula to datavalidation
            dataValidation1.Append(formula11);

            //append the datavalidation to datavalidations
            dataValidations1.Append(dataValidation1);

            WorkSheetPart.Worksheet.Save();

        }

        /// <summary>
        /// Lock Specified Cells
        /// </summary>
        /// <param name="SheetName"> Sheet Name</param>
        /// <param name="CellAddresses">string array that contains the range/cell address </param>
        /// <param name="password">password</param>
        public void LockCells(string SheetName, string[] CellAddresses, string password)
        {
            if (string.IsNullOrEmpty(SheetName))
            {
                throw new OpenXmlPackageException("Missing SheetName");
            }
            IEnumerable<Sheet> sheet = _excelDoc.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>().Where(s => s.Name == SheetName);
            if (sheet.Count() == 0)
            {
                OpenXmlPackageException openEx = new OpenXmlPackageException("WorkSheets not found");
                throw openEx;
            }

            string relationshipId = sheet.FirstOrDefault().Id.Value;
            WorkSheetPart = (WorksheetPart)_excelDoc.WorkbookPart.GetPartById(relationshipId);
            if (WorkSheetPart == null)
            {
                OpenXmlPackageException openEx = new OpenXmlPackageException("WorkSheets not found");
                throw openEx;
            }
            SheetData = WorkSheetPart.Worksheet.Elements<SheetData>().FirstOrDefault();
            IEnumerable<Cell> cells = SheetData.Descendants<Cell>();

            Worksheet worksheet = WorkSheetPart.Worksheet;


            //check if the sheet is already locked
            if (worksheet.Descendants<SheetProtection>().Count<SheetProtection>() > 0)
            {
                throw new Exception("Sheet Already locked");
            }
            // UNLOCK ALL the cells
            IEnumerable<CellFormat> cellFmtList = _excelDoc.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats
            .Descendants<CellFormat>();
            foreach (CellFormat cellFmt in cellFmtList)
            {
                cellFmt.ApplyProtection = true;
                if (cellFmt.ChildElements.First<Protection>() != null)
                {
                    //cellFmt.ChildElements.First<Protection>().Locked = false;
                }
                else
                {
                    cellFmt.AppendChild<Protection>(new Protection() { Locked = false });
                }
            }

            UInt32Value dSILock = 0;
            CellFormat CFDefaultLock = new CellFormat(new Protection() { Locked = true })
            {
                NumberFormatId = 0U,
                FontId = 0U,
                FillId = 0U,
                BorderId = 0U,
                ApplyProtection = true
            };

            //Check if the default Cell format is already present 
            //dSILock is the position of the fmt
            bool flgDSI = true;
            foreach (CellFormat cellFmt in cellFmtList)
            {
                if (CompareCellFormats(CFDefaultLock, cellFmt))
                {
                    flgDSI = false;
                    //cellFmt.Protection.Locked = true;
                    _excelDoc.WorkbookPart.WorkbookStylesPart.Stylesheet.Save();
                    break;
                }
                dSILock++;
            }


            //if the flag is false the default format is already present no need to add
            //else add and increment count
            CFDefaultLock.Protection.Locked = true;
            if (flgDSI)
            {
                _excelDoc.WorkbookPart.WorkbookStylesPart.Stylesheet
                                                    .CellFormats.AppendChild<CellFormat>(CFDefaultLock);
                dSILock = Convert.ToUInt32(_excelDoc.WorkbookPart.WorkbookStylesPart.Stylesheet
                                                    .CellFormats.ChildElements.Count - 1);
                _excelDoc.WorkbookPart.WorkbookStylesPart.Stylesheet
                                                    .CellFormats.Count = dSILock + 1;
                _excelDoc.WorkbookPart.WorkbookStylesPart.Stylesheet.Save();

            }

            for (int count = 0; count < CellAddresses.Length; count++)
            {
                string startAddress = CellAddresses[count];
                startAddress = startAddress.ToUpper();
                string[] splitAddress = { "", "", "", "" };
                //to check if more than one cell is present
                if (startAddress.Contains(':'))
                {
                    splitAddress = RangeDataSplit(startAddress);
                    string[] strSequence = GetColumnNameStringArray(splitAddress[0], splitAddress[2]);
                    for (int rowCount = Convert.ToInt32(splitAddress[1]); rowCount <= Convert.ToInt32(splitAddress[3]); rowCount++)
                    {
                        for (int strSeqCount = 0; strSeqCount < strSequence.Count<string>(); strSeqCount++)
                        {
                            if (cells.Where<Cell>(c => c.CellReference.Value == (StringValue)(strSequence[strSeqCount] + rowCount).ToString().Trim()).Count<Cell>() > 0)
                            {
                                Cell cell = cells.Where<Cell>(c => c.CellReference.Value == (StringValue)(strSequence[strSeqCount] + rowCount).ToString().Trim()).First<Cell>();
                                if (cell.StyleIndex != null)
                                {
                                    //Fetch CellFormat specifed by StyleIndex
                                    CellFormat CFtemp = null;
                                    uint ct = 0;
                                    foreach (CellFormat c in cellFmtList)
                                    {
                                        if (cell.StyleIndex.Value == ct)
                                        {
                                            CFtemp = (CellFormat)c.Clone();
                                            break;
                                        }
                                        ct++;
                                    }
                                    CFtemp.Protection.Locked = true;
                                    //Check if the format already exists
                                    bool flgNSI = true;
                                    uint SItemp = 0;
                                    foreach (CellFormat cellFmt in cellFmtList)
                                    {
                                        if (CompareCellFormats(CFtemp, cellFmt))
                                        {
                                            cell.StyleIndex = SItemp;
                                            worksheet.Save();
                                            flgNSI = false;
                                            break;
                                        }
                                        SItemp++;
                                    }
                                    #region dumb coding but working cool
                                    //bool flgNSI = true;
                                    //uint brkCond = 0;
                                    //uint SItemp = 0;
                                    //foreach (CellFormat cellFmt in cellFmtList)
                                    //{
                                    //    if (CompareCellFormats(CFtemp, cellFmt))
                                    //    {
                                    //        if (brkCond == 1)
                                    //        {
                                    //            cell.StyleIndex = SItemp;
                                    //            worksheet.Save();
                                    //            CFtemp.Protection.Locked = true;
                                    //            s.WorkbookPart.WorkbookStylesPart.Stylesheet.Save();
                                    //            flgNSI = false;
                                    //            break;
                                    //        }
                                    //        brkCond++;
                                    //    }
                                    //    SItemp++;
                                    //}
                                    #endregion
                                    //add if the Cell Format does not exists
                                    if (flgNSI)
                                    {
                                        _excelDoc.WorkbookPart.WorkbookStylesPart.Stylesheet
                                            .CellFormats.AppendChild<CellFormat>(CFtemp);
                                        _excelDoc.WorkbookPart.WorkbookStylesPart.Stylesheet
                                            .CellFormats.Count = Convert.ToUInt32(
                                                _excelDoc.WorkbookPart.WorkbookStylesPart.Stylesheet
                                                    .CellFormats.ChildElements.Count - 1) + 1;
                                        _excelDoc.WorkbookPart.WorkbookStylesPart.Stylesheet.Save();
                                        cell.StyleIndex = Convert.ToUInt32(
                                                           _excelDoc.WorkbookPart.WorkbookStylesPart.Stylesheet
                                                                .CellFormats.ChildElements.Count - 1);
                                        worksheet.Save();

                                    }
                                    //Response.Write((iLcellFmtList[(int)cell.StyleIndex.Value].Protection.Locked.Value).ToString() + "<br/>");
                                }
                                else
                                {
                                    cell.StyleIndex = dSILock;
                                    worksheet.Save();
                                }
                            }
                        }
                    }
                }
                else
                {
                    if (cells.Where<Cell>(c => c.CellReference.Value == (StringValue)CellAddresses[count]).Count<Cell>() > 0)
                    {
                        Cell cell = cells.Where<Cell>(c => c.CellReference.Value == (StringValue)CellAddresses[count]).First<Cell>();

                        //Response.Write(cell.CellReference.ToString() + "<br/>");
                        if (cell.StyleIndex != null)
                        {
                            //COPY PASTE FROM THE OTHER PART
                            //Fetch CellFormat specifed by StyleIndex
                            CellFormat CFtemp = null;
                            uint ct = 0;
                            foreach (CellFormat c in cellFmtList)
                            {
                                if (cell.StyleIndex.Value == ct)
                                {
                                    CFtemp = (CellFormat)c.Clone();
                                    break;
                                }
                                ct++;
                            }
                            CFtemp.Protection.Locked = true;
                            //Check if the format already exists
                            bool flgNSI = true;
                            uint SItemp = 0;
                            foreach (CellFormat cellFmt in cellFmtList)
                            {
                                if (CompareCellFormats(CFtemp, cellFmt))
                                {
                                    cell.StyleIndex = SItemp;
                                    worksheet.Save();
                                    flgNSI = false;
                                    break;
                                }
                                SItemp++;
                            }
                            //add if the Cell Format does not exists
                            if (flgNSI)
                            {
                                _excelDoc.WorkbookPart.WorkbookStylesPart.Stylesheet
                                    .CellFormats.AppendChild<CellFormat>(CFtemp);
                                _excelDoc.WorkbookPart.WorkbookStylesPart.Stylesheet
                                    .CellFormats.Count = Convert.ToUInt32(
                                        _excelDoc.WorkbookPart.WorkbookStylesPart.Stylesheet
                                            .CellFormats.ChildElements.Count - 1) + 1;
                                _excelDoc.WorkbookPart.WorkbookStylesPart.Stylesheet.Save();
                                cell.StyleIndex = Convert.ToUInt32(
                                                    _excelDoc.WorkbookPart.WorkbookStylesPart.Stylesheet
                                                        .CellFormats.ChildElements.Count - 1);
                                worksheet.Save();

                            }
                            //Response.Write("Present" +cell.StyleIndex+  "<br/>");
                        }
                        else
                        {
                            cell.StyleIndex = dSILock;
                            worksheet.Save();
                        }
                    }
                }
            }
            ////Add sheet protection part
            //ASCIIEncoding StringToByte = new ASCIIEncoding();
            //byte[] bytePwd = StringToByte.GetBytes(password);


            worksheet.InsertAfter<SheetProtection>(new SheetProtection() { Password = GetSheetPassword(password), Sheet = true, Objects = true, Scenarios = true }, SheetData);
            worksheet.Save();

        }
        /// <summary>
        /// Unlock Cells
        /// </summary>
        /// <param name="SheetName">Sheet Name</param>
        /// <param name="CellAddress">Cell address</param>
        /// <param name="password">password</param>
        public void UnLockCells(string SheetName, string[] CellAddress, string password)
        {
            if (string.IsNullOrEmpty(SheetName))
            {
                throw new OpenXmlPackageException("Missing SheetName");
            }
            IEnumerable<Sheet> sheet = _excelDoc.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>().Where(s => s.Name == SheetName);
            if (sheet.Count() == 0)
            {
                OpenXmlPackageException openEx = new OpenXmlPackageException("WorkSheets not found");
                throw openEx;
            }

            string relationshipId = sheet.FirstOrDefault().Id.Value;
            WorkSheetPart = (WorksheetPart)_excelDoc.WorkbookPart.GetPartById(relationshipId);
            if (WorkSheetPart == null)
            {
                OpenXmlPackageException openEx = new OpenXmlPackageException("WorkSheets not found");
                throw openEx;
            }
            SheetData = WorkSheetPart.Worksheet.Elements<SheetData>().FirstOrDefault();
            IEnumerable<Cell> cells = SheetData.Descendants<Cell>();
            Worksheet worksheet = WorkSheetPart.Worksheet;
            //check if the sheet is already unlocked
            if (worksheet.Descendants<SheetProtection>().Count<SheetProtection>() == 0)
            {
                return;
            }

            //Check if the provided password is valid

            if (worksheet.Descendants<SheetProtection>().Count<SheetProtection>() > 0)
            {
                StringValue strChkPwd = worksheet.Descendants<SheetProtection>().First<SheetProtection>().Password.ToString();
                if (GetSheetPassword(password) != strChkPwd)
                {
                    OpenXmlPackageException openEx = new OpenXmlPackageException("Wrong Password");
                    throw openEx;
                }
            }


            // Set the protection.locked property of cells which do no have any protection to false
            IEnumerable<CellFormat> cellFmtList = _excelDoc.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats
            .Descendants<CellFormat>();
            //foreach (CellFormat cellFmt in cellFmtList)
            //{
            //    cellFmt.ApplyProtection = true;
            //    if (cellFmt.ChildElements.First<Protection>() == null)
            //    {
            //        cellFmt.AppendChild<Protection>(new Protection() { Locked = false });
            //    }
            //}


            for (int count = 0; count < CellAddress.Length; count++)
            {
                string startAddress = CellAddress[count];
                startAddress = startAddress.ToUpper();
                string[] splitAddress = { "", "", "", "" };
                //to check if more than one cell is present
                if (startAddress.Contains(':'))
                {
                    splitAddress = RangeDataSplit(startAddress);
                    string[] strSequence = GetColumnNameStringArray(splitAddress[0], splitAddress[2]);
                    for (int rowCount = Convert.ToInt32(splitAddress[1]); rowCount <= Convert.ToInt32(splitAddress[3]); rowCount++)
                    {
                        for (int strSeqCount = 0; strSeqCount < strSequence.Count<string>(); strSeqCount++)
                        {
                            if (cells.Where<Cell>(c => c.CellReference.Value == (StringValue)(strSequence[strSeqCount] + rowCount).ToString().Trim()).Count<Cell>() > 0)
                            {
                                Cell cell = cells.Where<Cell>(c => c.CellReference.Value == (StringValue)(strSequence[strSeqCount] + rowCount).ToString().Trim()).First<Cell>();
                                if (cell.StyleIndex != null)
                                {
                                    //Fetch CellFormat specifed by StyleIndex
                                    CellFormat CFtemp = null;
                                    uint ct = 0;
                                    foreach (CellFormat c in cellFmtList)
                                    {
                                        if (cell.StyleIndex.Value == ct)
                                        {
                                            CFtemp = (CellFormat)c.Clone();
                                            break;
                                        }
                                        ct++;
                                    }
                                    CFtemp.AppendChild<Protection>(new Protection() { Locked = false });

                                    //Check if the format already exists
                                    bool flgNSI = true;
                                    uint SItemp = 0;
                                    foreach (CellFormat cellFmt in cellFmtList)
                                    {
                                        if (CompareCellFormats(CFtemp, cellFmt))
                                        {
                                            cell.StyleIndex = SItemp;
                                            worksheet.Save();
                                            flgNSI = false;
                                            break;
                                        }
                                        SItemp++;
                                    }
                                    //add if the Cell Format does not exists
                                    if (flgNSI)
                                    {
                                        _excelDoc.WorkbookPart.WorkbookStylesPart.Stylesheet
                                            .CellFormats.AppendChild<CellFormat>(CFtemp);
                                        _excelDoc.WorkbookPart.WorkbookStylesPart.Stylesheet
                                            .CellFormats.Count = Convert.ToUInt32(
                                               _excelDoc.WorkbookPart.WorkbookStylesPart.Stylesheet
                                                    .CellFormats.ChildElements.Count - 1) + 1;
                                        _excelDoc.WorkbookPart.WorkbookStylesPart.Stylesheet.Save();
                                        cell.StyleIndex = Convert.ToUInt32(
                                                            _excelDoc.WorkbookPart.WorkbookStylesPart.Stylesheet
                                                                .CellFormats.ChildElements.Count - 1);
                                        worksheet.Save();

                                    }
                                    //Response.Write((iLcellFmtList[(int)cell.StyleIndex.Value].Protection.Locked.Value).ToString() + "<br/>");
                                }
                                else
                                {
                                    cell.StyleIndex = 0;
                                    worksheet.Save();
                                }
                            }
                        }
                    }
                }
                else
                {
                    if (cells.Where<Cell>(c => c.CellReference.Value == (StringValue)CellAddress[count]).Count<Cell>() > 0)
                    {
                        Cell cell = cells.Where<Cell>(c => c.CellReference.Value == (StringValue)CellAddress[count]).First<Cell>();

                        //Response.Write(cell.CellReference.ToString() + "<br/>");
                        if (cell.StyleIndex != null)
                        {
                            //COPY PASTE FROM THE OTHER PART
                            //Fetch CellFormat specifed by StyleIndex
                            CellFormat CFtemp = null;
                            uint ct = 0;
                            foreach (CellFormat c in cellFmtList)
                            {
                                if (cell.StyleIndex.Value == ct)
                                {
                                    CFtemp = (CellFormat)c.Clone();
                                    break;
                                }
                                ct++;
                            }
                            CFtemp.Protection.Locked = false;
                            //Check if the format already exists
                            bool flgNSI = true;
                            uint SItemp = 0;
                            foreach (CellFormat cellFmt in cellFmtList)
                            {
                                if (CompareCellFormats(CFtemp, cellFmt))
                                {
                                    cell.StyleIndex = SItemp;
                                    worksheet.Save();
                                    flgNSI = false;
                                    break;
                                }
                                SItemp++;
                            }
                            //add if the Cell Format does not exists
                            if (flgNSI)
                            {
                                _excelDoc.WorkbookPart.WorkbookStylesPart.Stylesheet
                                    .CellFormats.AppendChild<CellFormat>(CFtemp);
                                _excelDoc.WorkbookPart.WorkbookStylesPart.Stylesheet
                                    .CellFormats.Count = Convert.ToUInt32(
                                        _excelDoc.WorkbookPart.WorkbookStylesPart.Stylesheet
                                            .CellFormats.ChildElements.Count - 1) + 1;
                                _excelDoc.WorkbookPart.WorkbookStylesPart.Stylesheet.Save();
                                cell.StyleIndex = Convert.ToUInt32(
                                                    _excelDoc.WorkbookPart.WorkbookStylesPart.Stylesheet
                                                        .CellFormats.ChildElements.Count - 1);
                                worksheet.Save();

                            }


                            //Response.Write("Present" +cell.StyleIndex+  "<br/>");
                        }
                        else
                        {
                            cell.StyleIndex = 0;

                            worksheet.Save();
                        }
                    }
                }
            }

        }

        /// <summary>
        /// Lock the specified column
        /// </summary>
        /// <param name="SheetName">Sheet Name</param>
        /// <param name="columnNo">Sheet Name</param>
        /// <param name="password">Password</param>
        public void LockColumn(string SheetName, int columnNo, string password)
        {
            if (string.IsNullOrEmpty(SheetName))
            {
                throw new OpenXmlPackageException("Missing SheetName");
            }
            IEnumerable<Sheet> sheet = _excelDoc.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>().Where(s => s.Name == SheetName);
            if (sheet.Count() == 0)
            {
                OpenXmlPackageException openEx = new OpenXmlPackageException("WorkSheets not found");
                throw openEx;
            }

            string relationshipId = sheet.FirstOrDefault().Id.Value;
            WorkSheetPart = (WorksheetPart)_excelDoc.WorkbookPart.GetPartById(relationshipId);
            if (WorkSheetPart == null)
            {
                OpenXmlPackageException openEx = new OpenXmlPackageException("WorkSheets not found");
                throw openEx;
            }
            SheetData = WorkSheetPart.Worksheet.Elements<SheetData>().FirstOrDefault();


            Worksheet worksheet = WorkSheetPart.Worksheet;


            //check if the sheet is already locked
            if (worksheet.Descendants<SheetProtection>().Count<SheetProtection>() > 0)
            {
                throw new Exception("Sheet Already locked");
            }
            // UNLOCK ALL the cells
            IEnumerable<CellFormat> cellFmtList = _excelDoc.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats
            .Descendants<CellFormat>();
            foreach (CellFormat cellFmt in cellFmtList)
            {
                cellFmt.ApplyProtection = true;
                if (cellFmt.ChildElements.First<Protection>() != null)
                {
                    //cellFmt.ChildElements.First<Protection>().Locked = false;
                }
                else
                {
                    cellFmt.AppendChild<Protection>(new Protection() { Locked = false });
                }
            }

            UInt32Value dSILock = 0;
            CellFormat CFDefaultLock = new CellFormat(new Protection() { Locked = true })
            {
                NumberFormatId = 0U,
                FontId = 0U,
                FillId = 0U,
                BorderId = 0U,
                ApplyProtection = true
            };
            //Check if the default Cell format is already present 
            //dSILock is the position of the fmt
            bool flgDSI = true;
            foreach (CellFormat cellFmt in cellFmtList)
            {
                if (CompareCellFormats(CFDefaultLock, cellFmt))
                {
                    flgDSI = false;
                    //cellFmt.Protection.Locked = true;
                    _excelDoc.WorkbookPart.WorkbookStylesPart.Stylesheet.Save();
                    break;
                }
                dSILock++;
            }


            //if the flag is false the default format is already present no need to add
            //else add and increment count
            CFDefaultLock.Protection.Locked = true;
            if (flgDSI)
            {
                _excelDoc.WorkbookPart.WorkbookStylesPart.Stylesheet
                                                    .CellFormats.AppendChild<CellFormat>(CFDefaultLock);
                dSILock = Convert.ToUInt32(_excelDoc.WorkbookPart.WorkbookStylesPart.Stylesheet
                                                    .CellFormats.ChildElements.Count - 1);
                _excelDoc.WorkbookPart.WorkbookStylesPart.Stylesheet
                                                    .CellFormats.Count = dSILock + 1;
                _excelDoc.WorkbookPart.WorkbookStylesPart.Stylesheet.Save();

            }
            Columns columns = WorkSheetPart.Worksheet.Elements<Columns>().FirstOrDefault();

            if (columns == null)
            {
                if (columns == null)
                {
                    columns = new Columns();
                }
                // Min & Max refer to the 1-indexed column ordinal 
                Column column3 = new Column() { Min = (UInt32)columnNo, Max = (UInt32)columnNo, Width = 9.140625D, CustomWidth = true, Style = (UInt32Value)dSILock };
                //Column column8 = new Column() { Min = (UInt32Value)8U, Max = (UInt32Value)8U, Width = 0D, CustomWidth = true, Hidden = true };
                // ... repeat for each column  
                columns.Append(column3);
                //columns.Append(column8);

                if (WorkSheetPart.Worksheet.Elements<Columns>().FirstOrDefault() == null)
                {
                    SheetFormatProperties sheetformatproperties = WorkSheetPart.Worksheet.GetFirstChild<SheetFormatProperties>();
                    WorkSheetPart.Worksheet.InsertAfter(columns, sheetformatproperties);
                }
            }
            else
            {

                Column col = columns.Elements<Column>().Where(r => r.Max == columnNo).FirstOrDefault();
                if (col == null)
                {
                    Column column3 = new Column() { Min = (UInt32)columnNo, Max = (UInt32)columnNo, Width = 9.140625D, CustomWidth = true, Style = (UInt32Value)dSILock };
                    //Column column8 = new Column() { Min = (UInt32Value)8U, Max = (UInt32Value)8U, Width = 0D, CustomWidth = true, Hidden = true };
                    // ... repeat for each column  
                    columns.Append(column3);
                    //columns.Append(column8);

                    if (WorkSheetPart.Worksheet.Elements<Columns>().FirstOrDefault() == null)
                    {
                        SheetFormatProperties sheetformatproperties = WorkSheetPart.Worksheet.GetFirstChild<SheetFormatProperties>();
                        WorkSheetPart.Worksheet.InsertAfter(columns, sheetformatproperties);
                    }

                }
                else
                {
                    col.Style = (UInt32Value)dSILock;
                }
            }
            //add sheetprotection to worksheet
            worksheet.InsertAfter<SheetProtection>(new SheetProtection() { Password = GetSheetPassword(password), Sheet = true, Objects = true, Scenarios = true }, SheetData);
            worksheet.Save();

        }

        /// <summary>
        /// Unlock column
        /// </summary>
        /// <param name="SheetName">Sheet Name</param>
        /// <param name="columnNo">coulmn number</param>
        /// <param name="password">password</param>
        public void UnLockColumn(string SheetName, int columnNo, string password)
        {

            if (string.IsNullOrEmpty(SheetName))
            {
                throw new OpenXmlPackageException("Missing SheetName");
            }
            IEnumerable<Sheet> sheet = _excelDoc.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>().Where(s => s.Name == SheetName);
            if (sheet.Count() == 0)
            {
                OpenXmlPackageException openEx = new OpenXmlPackageException("WorkSheets not found");
                throw openEx;
            }

            string relationshipId = sheet.FirstOrDefault().Id.Value;
            WorkSheetPart = (WorksheetPart)_excelDoc.WorkbookPart.GetPartById(relationshipId);
            if (WorkSheetPart == null)
            {
                OpenXmlPackageException openEx = new OpenXmlPackageException("WorkSheets not found");
                throw openEx;
            }
            SheetData = WorkSheetPart.Worksheet.Elements<SheetData>().FirstOrDefault();
            Worksheet worksheet = WorkSheetPart.Worksheet;
            //check if the sheet is already unlocked
            if (worksheet.Descendants<SheetProtection>().Count<SheetProtection>() == 0)
            {
                return;
            }

            //Check if the provided password is valid

            if (worksheet.Descendants<SheetProtection>().Count<SheetProtection>() > 0)
            {
                StringValue strChkPwd = worksheet.Descendants<SheetProtection>().First<SheetProtection>().Password.ToString();
                if (GetSheetPassword(password) != strChkPwd)
                {
                    OpenXmlPackageException openEx = new OpenXmlPackageException("Wrong Password");
                    throw openEx;
                }
            }


            // Set the protection.locked property of cells which do no have any protection to false
            IEnumerable<CellFormat> cellFmtList = _excelDoc.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats
            .Descendants<CellFormat>();
            foreach (CellFormat cellFmt in cellFmtList)
            {
                cellFmt.ApplyProtection = true;
                if (cellFmt.ChildElements.First<Protection>() == null)
                {
                    //cellFmt.AppendChild<Protection>(new Protection() { Locked = false });
                }
            }
            //  Column col = WorkSheetPart.Worksheet.Elements<Column>().Where(r => r.Max == columnNo).FirstOrDefault();
            Columns columns = WorkSheetPart.Worksheet.Elements<Columns>().FirstOrDefault();
            Column col = columns.Elements<Column>().Where(r => r.Max == columnNo).FirstOrDefault();
            if (col == null)
            {
                throw new OpenXmlPackageException("Column is not locked");
            }
            else
            {

                if (col.Style.Value != null)
                {
                    //Fetch CellFormat specifed by StyleIndex
                    CellFormat CFtemp = null;
                    uint ct = 0;
                    foreach (CellFormat c in cellFmtList)
                    {
                        if (col.Style.Value == ct)
                        {
                            CFtemp = (CellFormat)c.Clone();
                            break;
                        }
                        ct++;
                    }
                    CFtemp.AppendChild<Protection>(new Protection() { Locked = false });
                    //Check if the format already exists
                    bool flgNSI = true;
                    uint SItemp = 0;
                    foreach (CellFormat cellFmt in cellFmtList)
                    {
                        if (CompareCellFormats(CFtemp, cellFmt))
                        {
                            col.Style.Value = SItemp;
                            worksheet.Save();
                            flgNSI = false;
                            break;
                        }
                        SItemp++;
                    }
                    //add if the Cell Format does not exists
                    if (flgNSI)
                    {
                        _excelDoc.WorkbookPart.WorkbookStylesPart.Stylesheet
                            .CellFormats.AppendChild<CellFormat>(CFtemp);
                        _excelDoc.WorkbookPart.WorkbookStylesPart.Stylesheet
                            .CellFormats.Count = Convert.ToUInt32(
                               _excelDoc.WorkbookPart.WorkbookStylesPart.Stylesheet
                                    .CellFormats.ChildElements.Count - 1) + 1;
                        _excelDoc.WorkbookPart.WorkbookStylesPart.Stylesheet.Save();
                        col.Style.Value = Convert.ToUInt32(
                                            _excelDoc.WorkbookPart.WorkbookStylesPart.Stylesheet
                                                .CellFormats.ChildElements.Count - 1);
                        worksheet.Save();

                    }
                    //Response.Write((iLcellFmtList[(int)cell.StyleIndex.Value].Protection.Locked.Value).ToString() + "<br/>");
                }
                else
                {
                    col.Style.Value = 0;

                    worksheet.Save();
                }


            }
        }


        /// <summary>
        /// Inserts Data from DataTable into excel
        /// </summary>
        /// <param name="excelData">DataTable Param</param>
        /// <param name="cellRef">Cell Reference to start writing data</param>
        public void SettingCellValue(string cellVaue, string cellRef)
        {
            try
            {
                int rowIndex = 1;
                //row index 
                GetRowIndexFromCellRefrence(cellRef, out rowIndex);
                //get the column index
                uint lastRowIndex = (uint)rowIndex;
                uint columnIndex = GetColumnIndexFromColumnName(GetColumnNameFromCellRefrence(cellRef));
                Row dataRow = GetRow(lastRowIndex, true);
                int colIndex = (int)columnIndex;
                Cell cell = GetCell(dataRow, colIndex, lastRowIndex);

                CellValueInsert(cell, cellVaue);
            }
            catch (OpenXmlPackageException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        public int isColumnHide(string worksheetName, int columnno)
        {

            if (string.IsNullOrEmpty(worksheetName))
            {
                throw new OpenXmlPackageException("Missing SheetName");
            }

            IEnumerable<Sheet> sheets = _excelDoc.WorkbookPart.Workbook.Descendants<Sheet>();

            foreach (Sheet item in sheets)
            {
                WorkSheetPart = (WorksheetPart)_excelDoc.WorkbookPart.GetPartById(item.Id);
                if (item.Name == worksheetName)
                {
                    SheetData = WorkSheetPart.Worksheet.Elements<SheetData>().FirstOrDefault();
                    Columns columns = WorkSheetPart.Worksheet.Elements<Columns>().FirstOrDefault();
                    List<int> min=new List<int> ();
                    List<int> max=new List<int> ();
                    List<Column> lstcol = new List<DocumentFormat.OpenXml.Spreadsheet.Column>();
                    
                        int tempmin = 0;
                        int tempmax = 0;
                        var res = from col in WorkSheetPart.Worksheet.Elements<Column>()
                                  select new
                                      { 

                                      };

                    //Column col = WorkSheetPart.Worksheet.Elements<Column>().Where(r => r.Max == columnno).FirstOrDefault();
                    
                    
                    //if (col == null)
                    //{
                    //    if (columns == null)
                    //    {
                    //        columns = new Columns();
                    //    }
                    //    // Min & Max refer to the 1-indexed column ordinal 
                    //    Column currentcol = new Column() { Min = (UInt32)columnno, Max = (UInt32)columnno, Width = 0D, CustomWidth = true, Hidden = true };
                    //    // ... repeat for each column  
                    //    columns.Append(currentcol);

                    //    if (WorkSheetPart.Worksheet.Elements<Columns>().FirstOrDefault() == null)
                    //    {
                    //        SheetFormatProperties sheetformatproperties = WorkSheetPart.Worksheet.GetFirstChild<SheetFormatProperties>();
                    //        // insert after sheet format properties
                    //        WorkSheetPart.Worksheet.InsertAfter(columns, sheetformatproperties);
                    //    }
                    //}
                    //else { col.Hidden = true; }

                    WorkSheetPart.Worksheet.Save();

                }

               
            }
            return 1;
        }
        #endregion

        #region Static Methods
        /// <summary>
        /// Gets Color
        /// </summary>
        /// <param name="sysColor"></param>
        /// <returns></returns>
        public static Color GetColor(System.Drawing.Color sysColor)
        {
            Color color = new Color()
            {
                Rgb = new HexBinaryValue()
                {
                    Value =
                        System.Drawing.ColorTranslator.ToHtml(
                            System.Drawing.Color.FromArgb(
                                sysColor.A,
                                sysColor.R,
                                sysColor.G,
                                sysColor.B)).Replace("#", "")
                }
            };
            return color;
        }
        /// <summary>
        /// Get Fill Object
        /// </summary>
        /// <param name="sysColor"></param>
        /// <returns></returns>
        public Fill GetFillColor(System.Drawing.Color sysColor)
        {
            Fill fill = new Fill(
                new PatternFill(
                    new ForegroundColor()
                    {
                        Rgb = new HexBinaryValue()
                        {
                            Value =
                                System.Drawing.ColorTranslator.ToHtml(
                                    System.Drawing.Color.FromArgb(
                                        sysColor.A,
                                        sysColor.R,
                                        sysColor.G,
                                        sysColor.B)).Replace("#", "")
                        }
                    })
                {
                    PatternType = PatternValues.Solid
                }
            );
            return fill;
        }
        #endregion


        #region Helper Methods

        /// <summary>
        /// Prepares Cell with values
        /// </summary>
        /// <param name="str"></param>
        /// <param name="dataType"></param>
        /// <returns></returns>
        /// 

        public void CellValueInsert(Cell cell, object objData)
        {

            string objDataType = objData.GetType().ToString();

            //Add text to text cell

            if (objDataType.Contains(TypeCode.Int32.ToString()) || objDataType.Contains(TypeCode.Int64.ToString()) || objDataType.Contains(TypeCode.Decimal.ToString()))
            {

                if (!string.IsNullOrEmpty(objData.ToString().Trim()))
                {

                    //objData = string.Empty;

                    cell.DataType = new EnumValue<CellValues>(CellValues.Number);

                    cell.CellValue = new CellValue(objData.ToString());

                }

            }

            else if (objDataType.Contains(TypeCode.DateTime.ToString()))
            {

                if (!string.IsNullOrEmpty(objData.ToString().Trim()))
                {
                    //double d;
                    //DateTime date = Convert.ToDateTime(objData);
                    //d = date.ToOADate();
                    //cell.CellValue = new CellValue(d.ToString());

                    //objData = string.Empty;
                    //cell.DataType = new EnumValue<CellValues>(CellValues.Date);
                   // cell.CellValue = new CellValue(objData.ToString());
                   
                   cell.CellValue = new CellValue(((DateTime)objData).ToOADate().ToString());
                   //cell.CellValue = new CellValue(((DateTime)objData).ToString());
                    
                    //cell.StyleIndex = 1;
                    //cell.DataType = new EnumValue<CellValues>(CellValues.Date);

                    //cell.DataType = new EnumValue<CellValues>(CellValues.Date);

                }

            }

            else
            {

                if (!string.IsNullOrEmpty(objData.ToString().Trim()))
                {

                    //objData = string.Empty;

                    cell.CellValue = new CellValue(objData.ToString());

                    cell.DataType = new EnumValue<CellValues>(CellValues.String);

                }

            }

            return;

        }

        //private Cell CellValueInsert(string str, string dataType)
        //{

        //    Cell cell = new Cell();

        //    //Add text to text cell
        //    if (dataType.Contains(TypeCode.Int32.ToString()) || dataType.Contains(TypeCode.Int64.ToString()) || dataType.Contains(TypeCode.Decimal.ToString()))
        //    {
        //        cell.DataType = new EnumValue<CellValues>(CellValues.Number);
        //        cell.CellValue = new CellValue(str);
        //    }
        //    else
        //    {
        //        cell.CellValue = new CellValue(str);
        //        cell.DataType = new EnumValue<CellValues>(CellValues.String);
        //    }

        //    return cell;
        //}

        /// <summary>
        /// 
        /// </summary>
        /// <param name="cell"></param>
        /// <param name="objData"></param>
        /// 
        //private void CellValueInsert(Cell cell, object objData)
        //{

        //    string objDataType = objData.GetType().ToString();
            
        //    //Add text to text cell
        //    if (objDataType.Contains(TypeCode.Int32.ToString()) || objDataType.Contains(TypeCode.Int64.ToString()) || objDataType.Contains(TypeCode.Decimal.ToString()))
        //    {
        //        cell.DataType = new EnumValue<CellValues>(CellValues.Number);
        //        //cell.CellValue = new CellValue(objData.ToString()); 
        //        cell.CellValue = new CellValue(objData.ToString()); 
                
        //    }
        //    else
        //    {
        //        cell.CellValue = new CellValue(objData.ToString());
        //        cell.DataType = new EnumValue<CellValues>(CellValues.String);
        //    }

        //    return;
        //}
        private void CellValueInsertTEST(Cell cell, object objData,string datatype)
        {

            string objDataType = datatype;
            //Add text to text cell
            if (objDataType.Contains(TypeCode.Int32.ToString()) || objDataType.Contains(TypeCode.Int64.ToString()) || objDataType.Contains(TypeCode.Decimal.ToString()))
            {
                cell.DataType = new EnumValue<CellValues>(CellValues.Number);
                //cell.CellValue = new CellValue(objData.ToString()); 
                cell.CellValue = new CellValue(objData.ToString());

            }
            else
            {
                cell.CellValue = new CellValue(objData.ToString());
                cell.DataType = new EnumValue<CellValues>(CellValues.String);
            }

            return;
        }
        /// <summary>
        /// Gets the Cell object
        /// </summary>
        /// <param name="rowIndex"></param>
        /// <param name="columnIndex"></param>
        /// <returns></returns>
        public Cell GetCell(Row dataRow, int columnIndex, uint rowIndex)
        {

            Cell cell = null;

            if (SheetData == null)
            {
                OpenXmlPackageException openEx = new OpenXmlPackageException("No Sheet Selected");
                throw openEx;
            }

            string columnName = GetColumnName(columnIndex);

            cell = dataRow.Elements<Cell>().Where(c => string.Compare(c.CellReference.Value, columnName + rowIndex, true) == 0).FirstOrDefault();

            if (cell == null)
            {
                Cell currentCell = new Cell
                {
                    CellReference = columnName + rowIndex
                };
                Cell cellAfter = dataRow.Elements<Cell>().Where(c => GetColumnIndexFromColumnName(GetColumnNameFromCellRefrence(c.CellReference.Value)) > columnIndex).FirstOrDefault();
                if (cellAfter != null)
                    dataRow.InsertBefore(currentCell, cellAfter);
                else
                    dataRow.Append(currentCell);

                return dataRow.Elements<Cell>().Where(c => string.Compare(c.CellReference.Value, columnName + rowIndex, true) == 0).FirstOrDefault();
            }
            return cell;
        }

        /// <summary>
        /// Gets the Cell object
        /// </summary>
        /// <param name="cellReference"></param>
        /// <returns></returns>
        public Cell GetCell(string cellReference)
        {
            if (SheetData == null)
            {
                OpenXmlPackageException openEx = new OpenXmlPackageException("No Sheet Selected");
                throw openEx;
            }

            int rowIndex = 0;
            Row dataRow = null;
            Cell cell = null;

            if (GetRowIndexFromCellRefrence(cellReference, out rowIndex))
            {
                dataRow = GetRow((uint)rowIndex, true);
                cell = dataRow.Elements<Cell>().Where(c => string.Compare(c.CellReference.Value, cellReference, true) == 0).FirstOrDefault();
                if (cell == null)
                {
                    int rowindex;
                    int colindex = Column(cellReference, out rowindex);
                    //uint cellrefIndex = GetColumnIndexFromColumnName(cellReference);
                    uint cellrefIndex = (uint)colindex;

                    Cell currentCell = new Cell
                    {
                        CellReference = cellReference
                    };

                    int rowindex1;

                    Column(cellReference, out rowindex1);

                    // Cell cellAfter = dataRow.Elements<Cell>().Where(c => GetColumnIndexFromColumnName(c.CellReference.Value) > cellrefIndex).FirstOrDefault();
                    Cell cellAfter = dataRow.Elements<Cell>().Where(c => Column(c.CellReference, out rowindex1) > cellrefIndex).FirstOrDefault();
                    if (cellAfter != null)
                        dataRow.InsertBefore(currentCell, cellAfter);
                    else
                        dataRow.Append(currentCell);

                    return dataRow.Elements<Cell>().Where(c => string.Compare(c.CellReference.Value, cellReference, true) == 0).FirstOrDefault();
                }
                return cell;
            }
            else
            {
                OpenXmlPackageException openEx = new OpenXmlPackageException("Cell reference is not valid");
                throw openEx;
            }
        }

        /// <summary>
        /// Given Column Name returns Index 
        /// </summary>
        /// <param name="colName">(A - ZZ)</param>
        /// <returns></returns>
        private uint GetColumnIndexFromColumnName(string colName)
        {
            char[] columnName = colName.ToUpper().ToCharArray();
            uint value = 1;

            for (uint i = 0, k = (uint)columnName.Length - 1; i < columnName.Length; i++, k--)
            {
                uint alpabetIndex = ((uint)columnName[i]) - 64;
                uint delta = 0;
                // last column simply add it
                if (k == 0)
                {
                    delta = alpabetIndex - 1;
                }
                else
                {
                    // aggregate
                    if (alpabetIndex == 0)
                        delta = (26 * k);
                    else
                        delta = (alpabetIndex * 26 * k);
                }
                value += delta;
            }
            return value;
        }

        /// <summary>
        /// Given a worksheet and a row index, return the row. 
        /// </summary>
        /// <param name="rowIndex"></param>
        /// <param name="_isAddRow">True,False</param>
        /// <returns></returns>        
        public Row GetRow(uint rowIndex, bool _isAddRow = false)
        {
            if (SheetData == null)
            {
                OpenXmlPackageException openEx = new OpenXmlPackageException("No Sheet Selected");
                throw openEx;
            }

            Row row = SheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).FirstOrDefault();
            if (row == null && _isAddRow)
            {
                Row currentRow = new Row
                {
                    RowIndex = (UInt32)rowIndex
                };
                Row rowAfter = SheetData.Elements<Row>().Where(r => r.RowIndex > rowIndex).FirstOrDefault();
                if (rowAfter != null)
                {
                    SheetData.InsertBefore<Row>(currentRow, rowAfter);
                }
                else
                    SheetData.Append(currentRow);
                return SheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).First();
            }
            return row;
        }

        /// <summary>
        /// Returns the hased password that can be set to worksheet password attribute
        /// </summary>
        /// <param name="password">password</param>
        /// <returns>hashed password</returns>
        public String GetSheetPassword(String password)
        {
            Int32 pLength = password.Length;
            Int32 hash = 0;
            if (pLength == 0) return hash.ToString("X");

            for (Int32 i = pLength - 1; i >= 0; i--)
            {
                hash = hash >> 14 & 0x01 | hash << 1 & 0x7fff;
                hash ^= password[i];
            }
            hash = hash >> 14 & 0x01 | hash << 1 & 0x7fff;
            hash ^= 0x8000 | 'N' << 8 | 'K';
            hash ^= pLength;
            return hash.ToString("X");
        }

        /// <summary>
        /// Splits the range and returns it in an array
        /// </summary>
        /// <param name="strAddress">range address</param>
        /// <returns>string array with column address and row address</returns>
        public string[] RangeDataSplit(string strAddress)
        {
            string[] range, sD1, sD3 = { "", "", "", "" };
            range = strAddress.Split(':');
            for (int i = 0; i < range[0].Length; i++)
            {
                if ((range[0][i]) < ('A') || (range[0][i]) > ('Z'))
                {
                    range[0] = range[0].Insert(i, ":");
                    break;
                }
            }
            sD1 = range[0].Split(':');
            sD3[0] = sD1[0];
            sD3[1] = sD1[1];
            for (int i = 0; i < range[1].Length; i++)
            {
                if ((range[1][i]) < ('A') || (range[1][i]) > ('Z'))
                {
                    range[1] = range[1].Insert(i, ":");
                    break;
                }
            }
            sD1 = range[1].Split(':');
            sD3[2] = sD1[0];
            sD3[3] = sD1[1];
            return sD3;
        }

        /// <summary>
        /// returns the string array of column indexes from the colummstart and end name
        /// </summary>
        /// <param name="colName">column name start</param>
        /// <param name="colNameEnd">column name end</param>
        /// <returns></returns>
        private string[] GetColumnNameStringArray(string colName, string colNameEnd)
        {
            string[] strOutput;
            strOutput = new string[1000];
            colName = colName.PadLeft(3, ' '); //Padding Left of string with Spaces to Make its length 3
            colNameEnd = colNameEnd.PadLeft(3, ' ');
            char[] cArr = colName.ToCharArray(); //Convert String to Char Array
            int lCount = 0;
            for (lCount = 0; lCount < (1000); lCount++) //Loop to Generate the Series
            {
                //Storing Single Element of Series
                string stemp;
                stemp = string.Concat(cArr[0], cArr[1], cArr[2]);
                strOutput[lCount] = stemp;

                if (strOutput[lCount] == colNameEnd)
                {
                    break;
                }
                //Conditions to generate Series
                if (cArr[2] != 'Z') //Check if Third Place is not Alphabet 'Z'
                {
                    cArr[2]++;
                }
                else if (cArr[1] != 'Z') //If at Third Place a 'Z' occurs increment Alphabet at Second Place
                {
                    if (cArr[1] == ' ')
                        cArr[1] = 'A';
                    else
                        cArr[1]++;
                    cArr[2] = 'A';
                }
                else if (cArr[0] != 'Z') //If at Second Place a 'Z' occurs increment Alphabet at First Place
                {
                    if (cArr[0] == ' ')
                        cArr[0] = 'A';
                    else
                        cArr[0]++;
                    cArr[1] = 'A';
                    cArr[2] = 'A';
                }
                else //If at Second Place a 'Z' occurs increment Alphabet at First Place
                {
                    if (lCount != (999)) //Checking if its the last element to display, if not display error message
                        break;
                }
            }
            for (int i = 0; i <= lCount; i++)
            {
                strOutput[i] = strOutput[i].Trim();
            }
            string[] strOut;
            strOut = new string[lCount + 1];
            for (int i = 0; i <= lCount; i++)
            {
                strOut[i] = strOutput[i];
            }
            //Return the series
            return strOut;
        }

        /// <summary>
        /// returns the list of cells in a range
        /// </summary>
        /// <param name="startCellReference"></param>
        /// <param name="endCellReference"></param>
        /// <returns></returns>
        public List<string> GetAllCellsFromRange(string startCellReference, string endCellReference)
        {
            List<string> cells = new List<string>();
            int startrowindex;
            int endrowindex;
            int startcolindex = Column(startCellReference, out startrowindex);
            int endcolindex = Column(endCellReference, out endrowindex);

            for (int col = startcolindex; col <= endcolindex; col++)
            {
                for (int row = startrowindex; row <= endrowindex; row++)
                {
                    string cellreference = GetColumnName(col) + row.ToString();
                    cells.Add(cellreference);
                }

            }



            return cells;
        }

        /// <summary>
        /// Given Cell reference returns column Name
        /// </summary>
        /// <param name="cellName"></param>
        /// <returns></returns>
        private string GetColumnNameFromCellRefrence(string cellName)
        {
            Match m = Regex.Match(cellName, @"\D+");
            return m.Value;
        }

        /// <summary>
        /// Given Cell reference returns Row Index
        /// </summary>
        /// <param name="cellName"></param>
        /// <param name="RowName"></param>
        /// <returns></returns>
        private bool GetRowIndexFromCellRefrence(string cellName, out int RowName)
        {
            Match m = Regex.Match(cellName, @"\d+");
            return int.TryParse(m.Value, out RowName);
        }

        /// <summary>
        /// Returns the row index and column index from the cellreference
        /// </summary>
        /// <param name="CellReference">address of the Cell</param>
        /// <param name="RowNumber">out parameter row number</param>
        /// <returns>column index</returns>
        public int Column(string CellReference, out int RowNumber)
        {
            char[] characters = new char[26];
            CellReference = CellReference.ToUpper();
            System.Text.RegularExpressions.Regex reg = new System.Text.RegularExpressions.Regex("(?<column>[A-Z]+)(?<row>\\d+)");
            System.Text.RegularExpressions.Match mth = reg.Match(CellReference);
            if (mth.Success)
                CellReference = mth.Groups["column"].Value;
            RowNumber = int.Parse(mth.Groups["row"].Value);
            int col = 0;
            int i = 0;
            int prod = 0;
            i = CellReference.Length - 1;
            col = 0; prod = 0;
            int delta = 0;
            while (i >= 0)
            {
                delta = ((CellReference[col++] - 'A')) + 1;
                if (i > 0)
                    prod = prod + (delta * ((int)Math.Pow(characters.Length, i)));// +delta;                                     
                else
                    prod += (delta);
                i--;
            }
            int ColumnNumber = prod;
            return ColumnNumber;
        }

        /// <summary>
        /// Given Column Index returns Name
        /// </summary>
        /// <param name="columnIndex"></param>
        /// <returns></returns>
        public string GetColumnName(int columnIndex)
        {
            int dividend = columnIndex;
            string columnName = String.Empty;
            int modifier;

            while (dividend > 0)
            {
                modifier = (dividend - 1) % 26;
                columnName =
                    Convert.ToChar(65 + modifier).ToString() + columnName;
                dividend = (int)((dividend - modifier) / 26);
            }
            return columnName;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="font"></param>
        /// <returns></returns>
        private UInt32Value CreateFont(Font font)
        {

            UInt32Value result = null;

            if (font == null)
                return result;
            uint fontCount = 0;
            bool foundfont = false;
            //if (fontTable != null && fontTable.ContainsKey(font))
            //{
            //    fontCount = fontTable[font];
            //    return UInt32Value.ToUInt32(fontCount);
            //}
            foreach (Font font1 in fontTable.Keys)
            {
                fontCount++;
                if (font.Equals(font1))
                {
                    result = UInt32Value.ToUInt32(fontCount) - 1;
                    foundfont = true;
                    break;
                }

            }
            if (foundfont == false)
            {

                try
                {
                    StyleSheet.Fonts.Append(font);
                    result = StyleSheet.Fonts.Count;

                    fontTable.Add(font, (uint)result);
                    StyleSheet.Fonts.Count++;
                }
                catch (OpenXmlPackageException opx)
                {
                    throw opx;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            return result;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="fillColor"></param>
        /// <returns></returns>
        private UInt32Value CreateFill(Fill fillColor)
        {
            UInt32Value result = null;
            if (fillColor == null)
                return result;
            bool foundfill = false;
            uint fillCount = 0;
            //if (fillTable != null && fillTable.ContainsKey(fillColor))
            //{
            //    fillCount = fillTable[fillColor];
            //    return UInt32Value.ToUInt32(fillCount);
            //}

            foreach (Fill fill in fillTable.Keys)
            {
                fillCount++;
                if (fillColor.Equals(fill))
                {
                    result = UInt32Value.ToUInt32(fillCount) - 1;
                    foundfill = true;
                    break;
                }

            }

            if (foundfill == false)
            {
                try
                {
                    StyleSheet.Fills.Append((Fill)fillColor.Clone());
                    result = StyleSheet.Fills.Count;

                    fillTable.Add(fillColor, (uint)result);

                    StyleSheet.Fills.Count++;
                }
                catch (OpenXmlPackageException opx)
                {
                    throw opx;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            return result;

        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="border"></param>
        /// <returns></returns>
        private UInt32Value CreateBorder(Border border)
        {
            UInt32Value result = null;
            if (border == null)
                return result;

            uint borderCount = 0;
            bool foundborder = false;
            //if (borderTable != null && borderTable.ContainsKey(border))
            //{
            //    borderCount = borderTable[border];
            //    return UInt32Value.ToUInt32(borderCount);
            //}
            foreach (Border border1 in borderTable.Keys)
            {
                borderCount++;
                if (border.Equals(border1))
                {
                    result = UInt32Value.ToUInt32(borderCount) - 2;
                    foundborder = true;
                    break;
                }

            }
            if (foundborder == false)
            {
                try
                {
                    StyleSheet.Borders.Append(border);
                    result = StyleSheet.Borders.Count;
                    borderTable.Add(border, (uint)result);
                    StyleSheet.Borders.Count++;
                }
                catch (OpenXmlPackageException opx)
                {
                    throw opx;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            return result;
        }

        /*
         * -----------------------------------
         * */
        public void ApplyStyleToRangeTEMP(string SheetName, string startCellReference, string endCellReference, Font font, Fill fill, Border border, UInt32Value numberFormatId, Alignment alignment)
        {
            // gets all the cells in the given range.
            List<string> cells = GetAllCellsFromRange(startCellReference, endCellReference);
            UInt32Value formatStyleIndex;
            if (string.IsNullOrEmpty(SheetName))
            {
                throw new OpenXmlPackageException("Missing SheetName");
            }
            IEnumerable<Sheet> sheet = _excelDoc.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>().Where(s => s.Name == SheetName);
            if (sheet.Count() == 0)
            {
                OpenXmlPackageException openEx = new OpenXmlPackageException("WorkSheets not found");
                throw openEx;
            }

            string relationshipId = sheet.FirstOrDefault().Id.Value;
            WorkSheetPart = (WorksheetPart)_excelDoc.WorkbookPart.GetPartById(relationshipId);
            if (WorkSheetPart == null)
            {
                OpenXmlPackageException openEx = new OpenXmlPackageException("WorkSheets not found");
                throw openEx;
            }
            SheetData = WorkSheetPart.Worksheet.Elements<SheetData>().FirstOrDefault();

            //apply the style index to all cells in a range
            foreach (string cell in cells)
            {

                Cell cellName = GetCell(cell);
                //  style index , returns the 0 based index value of the cellformat from cellformatlist if exists or creates a new cell format
                formatStyleIndex = CreateCellFormatTEMP(CreateFont(font), CreateFill(fill), CreateBorder(border), numberFormatId, alignment, cellName);

                if (cellName != null && formatStyleIndex.HasValue)
                {

                    cellName.StyleIndex = formatStyleIndex;

                }
                else
                {
                    string message = "Style index is not proper";
                    if (cellName == null)
                        message = "Cell Not found";



                    OpenXmlPackageException openEx = new OpenXmlPackageException(message);
                    throw openEx;

                }
            }

            WorkSheetPart.Worksheet.Save();

        }
        private UInt32Value CreateCellFormatTEMP(UInt32Value fontIndex, UInt32Value fillIndex, UInt32Value borderIndex, UInt32Value numberFormatId, Alignment alignment, Cell cell)
        {
            CellFormat cellFormat = new CellFormat();
            cellFormat = (CellFormat)_excelDoc.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats.ElementAt((int)cell.StyleIndex.Value);
            //cellFormat.NumberFormatId = 0U;
            //cellFormat.FontId = 0U;
            //cellFormat.FillId = 0U;
            //cellFormat.BorderId = 0U;
            UInt32Value result = null;
            if (fontIndex != null)
                cellFormat.FontId = fontIndex;

            if (fillIndex != null)
                cellFormat.FillId = fillIndex;

            if (borderIndex != null)
                cellFormat.BorderId = borderIndex;

            if (numberFormatId != null)
            {
                cellFormat.NumberFormatId = numberFormatId;
                cellFormat.ApplyNumberFormat = BooleanValue.FromBoolean(true);
            }
            if (alignment != null)
            {
                // Alignment newalign = alignment;
                cellFormat.ApplyAlignment = true;
                Alignment newalign = new Alignment();
                newalign = (Alignment)alignment.Clone();
                cellFormat.Append(newalign);
            }
            try
            {
                bool foundCellFormat = false;
                //check if cellformat already exists in sytesheet add only if it doesnt exist
                IEnumerable<CellFormat> cellFmtList = StyleSheet.CellFormats.Descendants<CellFormat>();
                uint cellformatindex = 0;
                foreach (CellFormat cellFmt in cellFmtList)
                {
                    if (CompareCellFormats(cellFormat, cellFmt))
                    {
                        foundCellFormat = true;
                        result = cellformatindex;
                        break;
                    }
                    cellformatindex++;
                }

                if (foundCellFormat == false)
                {
                    StyleSheet.CellFormats.Append(cellFormat);
                    result = StyleSheet.CellFormats.Count;
                    StyleSheet.CellFormats.Count++;
                    StyleSheet.Save();

                }
            }
            catch (OpenXmlPackageException opx)
            {
                throw opx;
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return result;
        }


        /*------------------------------*/

        /// <summary>
        /// 
        /// </summary>
        /// <param name="styleSheet"></param>
        /// <param name="fontIndex"></param>
        /// <param name="fillIndex"></param>
        /// <param name="numberFormatId"></param>
        /// <returns></returns>
        private UInt32Value CreateCellFormat(UInt32Value fontIndex, UInt32Value fillIndex, UInt32Value borderIndex, UInt32Value numberFormatId, Alignment alignment)
        {
            CellFormat cellFormat = new CellFormat();
            cellFormat.NumberFormatId = 0U;
            cellFormat.FontId = 0U;
            cellFormat.FillId = 0U;
            cellFormat.BorderId = 0U;
            UInt32Value result = null;
            if (fontIndex != null)
                cellFormat.FontId = fontIndex;

            if (fillIndex != null)
                cellFormat.FillId = fillIndex;

            if (borderIndex != null)
                cellFormat.BorderId = borderIndex;

            if (numberFormatId != null)
            {
                cellFormat.NumberFormatId = numberFormatId;
                cellFormat.ApplyNumberFormat = BooleanValue.FromBoolean(true);
            }
            if (alignment != null)
            {
                // Alignment newalign = alignment;
                cellFormat.ApplyAlignment = true;

                Alignment newalign = new Alignment();
                newalign = (Alignment)alignment.Clone();
                cellFormat.Append(newalign);


            }
            try
            {
                bool foundCellFormat = false;


                //check if cellformat already exists in sytesheet add only if it doesnt exist
                IEnumerable<CellFormat> cellFmtList = StyleSheet.CellFormats.Descendants<CellFormat>();
                uint cellformatindex = 0;
                foreach (CellFormat cellFmt in cellFmtList)
                {
                    if (CompareCellFormats(cellFormat, cellFmt))
                    {
                        foundCellFormat = true;
                        result = cellformatindex;
                        break;
                    }
                    cellformatindex++;
                }

                if (foundCellFormat == false)
                {
                    StyleSheet.CellFormats.Append(cellFormat);
                    result = StyleSheet.CellFormats.Count;
                    StyleSheet.CellFormats.Count++;
                    StyleSheet.Save();

                }
            }
            catch (OpenXmlPackageException opx)
            {
                throw opx;
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return result;
        }

        /// <summary>
        /// Compares two cellformats(not null) returns true or false
        /// </summary>
        /// <param name="cellFmt1">cellformat1</param>
        /// <param name="cellFmt2">cellformat2</param>
        /// <returns>true/false</returns>
        private static bool CompareCellFormats(CellFormat cellFmt1, CellFormat cellFmt2)
        {
            //assumes that cellFmt1 and cellFmt2 are not null
            //Check that protection tag is available or not 
            if ((cellFmt1.Protection != null && cellFmt2.Protection == null)
                || (cellFmt1.Protection == null && cellFmt2.Protection != null))
            {
                return false;
            }
            //Protection part to be checked onli if this condition is satisfied
            if (cellFmt1.Protection != null && cellFmt2.Protection != null)
            {
                IList<OpenXmlAttribute> il1PrAttribute = cellFmt1.Protection.GetAttributes();
                IList<OpenXmlAttribute> il2PrAttribute = cellFmt2.Protection.GetAttributes();
                if (il2PrAttribute.Count == il1PrAttribute.Count)
                {
                    for (int i = 0; i < il2PrAttribute.Count; i++)
                    {
                        if (!il2PrAttribute.Contains(il1PrAttribute[i]))
                        {
                            return false;
                        }
                    }
                }
                else
                {
                    return false;
                }
            }
            IList<OpenXmlAttribute> ilC2attributes = cellFmt2.GetAttributes();
            IList<OpenXmlAttribute> ilC1attributes = cellFmt1.GetAttributes();
            if (ilC2attributes.Count == ilC1attributes.Count)
            {
                for (int i = 0; i < ilC2attributes.Count; i++)
                {
                    if (!ilC2attributes.Contains(ilC1attributes[i]))
                    {
                        return false;
                    }
                }
            }
            else
            {
                return false;
            }
            return true;
        }
        #endregion

        /// <summary>
        /// Close and saves the File
        /// </summary>
        public void Close()
        {
            if (_excelDoc != null)
                _excelDoc.Close();
        }

        /// <summary>
        /// Closes and Disposes the object
        /// </summary>
        public void Dispose()
        {

            //if (document != null)
            //{
            //    document.Close();
            //    document.Dispose();
            //}
            //if (m_package != null)
            //    m_package.Close();

            if (_excelDoc != null)
                this.Close();
            if (_excelDoc != null)
                _excelDoc.Dispose();
        }


    //-------------------------------------------------------------------------------------------

        #region Constructor
        /// <summary>
        /// Creates a new instance of a package basedc on the data provided.
        /// </summary>
        /// <param name="data">Byte array representing the package.</param>
        //public PackageHelper(string filePath)
        //{
        //    //// load the memory stream
        //    //m_packageData = new MemoryStream();
        //    //m_packageData.Write(data, 0, data.Length);

        //    // open the package
        //    m_package =
        //        Package.Open(filePath,
        //            FileMode.Open,
        //            FileAccess.ReadWrite); // use read write access
        //}
        #endregion

        #region Disposal Methods
        /// <summary>
        /// Close the package and the memory stream
        /// </summary>
        //public void Dispose()
        //{
        //    if (document != null)
        //    {
        //        document.Close();
        //        document.Dispose();
        //    }
        //    if (m_package != null)
        //    {
        //        m_package.Close();
        //    }
        //}
        #endregion

        #region Package Management Methods
        /// <summary>
        /// Creates a relationship from one package part to another.
        /// </summary>
        /// <param name="sourceUri">The uri of the source part.</param>
        /// <param name="targetUri">The relative path representing the location of the target part based on the source part.</param>
        /// <param name="relationshipType">The type of relationship to create.</param>
        /// <returns>The ID of the new relationship.</returns>
        private string CreateInternalRelationship(Uri sourceUri, Uri targetUri, string relationshipType)
        {
            // open the source part
            PackagePart sourcePart = m_package.GetPart(sourceUri);

            // create the relationship
            PackageRelationship relationship =
                sourcePart.CreateRelationship(
                    PackUriHelper.GetRelativeUri(sourceUri, targetUri),
                    TargetMode.Internal, relationshipType);

            // return the new rel id
            return relationship.Id;
        }

        /// <summary>
        /// Gets the SharedString Data in a HashTable
        /// </summary>
        /// <returns></returns>
        public Hashtable GetSharedString()
        {
            Hashtable htsharedstring = new Hashtable();
            string strId = string.Empty;
            string sheetUri = string.Empty;
            Uri workbookUri = new Uri(@"/xl/sharedStrings.xml", UriKind.Relative);
            XPathDocument workbookXml = GetReadOnlyPart(workbookUri);

            string exp = ".//def:si";
            XPathNavigator navigator = workbookXml.CreateNavigator();
            if (nsMgr == null)
            {
                XmlDocument xdc = new XmlDocument();
                nsMgr = new XmlNamespaceManager(xdc.NameTable);
                nsMgr.AddNamespace("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
                nsMgr.AddNamespace("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
                nsMgr.AddNamespace("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
                nsMgr.AddNamespace("def", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
            }
            XPathNodeIterator NodeIter = navigator.Select(exp, nsMgr);
            int refCount = 0;
            while (NodeIter.MoveNext())
            {
                htsharedstring.Add(refCount, NodeIter.Current.Value);
                refCount++;
            }
            return htsharedstring;

        }

        /// <summary>
        /// Opens the part and loads the XML into an XPathDocument.
        /// </summary>
        /// <param name="partUri">The uri of the part to open.</param>
        /// <returns>Read only XPathDocument containing the xml from the part.</returns>
        private XPathDocument GetReadOnlyPart(Uri partUri)
        {
            // retrieve the part
            PackagePart readOnlyPart = m_package.GetPart(partUri);

            // load the part into a XPathDocument
            using (Stream partStream = readOnlyPart.GetStream(FileMode.Open, FileAccess.Read))
                return new XPathDocument(partStream);
        }

        /// <summary>
        /// Gets the SheetId for sheet name 
        /// </summary>
        /// <param name="sheetName">sheet name</param>
        /// <returns>sheedid</returns>
        private string GetSheetId(string sheetName)
        {
            string strId = string.Empty;
            string sheetUri = string.Empty;
            Uri workbookUri = new Uri(@"/xl/workbook.xml", UriKind.Relative);
            XPathDocument workbookXml = GetReadOnlyPart(workbookUri);

            string exp = "/def:workbook/def:sheets/def:sheet[@name=\"" + sheetName + "\"]/@r:id";
            XPathNavigator navigator = workbookXml.CreateNavigator();
            if (nsMgr == null)
            {
                XmlDocument xdc = new XmlDocument();
                nsMgr = new XmlNamespaceManager(xdc.NameTable);
                nsMgr.AddNamespace("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");

                nsMgr.AddNamespace("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
                nsMgr.AddNamespace("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
                nsMgr.AddNamespace("def", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
            }
            XPathNodeIterator NodeIter = navigator.Select(exp, nsMgr);
            if (NodeIter.MoveNext())
            {
                strId = NodeIter.Current.Value;
            }

            XmlDocument xmlDoc = new XmlDocument();
            XmlNamespaceManager nsMgr1 = new XmlNamespaceManager(xmlDoc.NameTable);
            nsMgr1.AddNamespace("def", "http://schemas.openxmlformats.org/package/2006/relationships");
            Uri relsUri = new Uri(@"/xl/_rels/workbook.xml.rels", UriKind.Relative);
            workbookXml = GetReadOnlyPart(relsUri);
            exp = "/def:Relationships/def:Relationship[@Id=\"" + strId + "\"]/@Target";
            navigator = workbookXml.CreateNavigator();
            NodeIter = navigator.Select(exp, nsMgr1);

            if (NodeIter.MoveNext())
            {
                sheetUri = NodeIter.Current.Value;
            }
            return sheetUri;
        }

        /// <summary>
        /// Gets the Column Name 
        /// </summary>
        /// <param name="cellAddr">Cell Address</param>
        /// <returns>Column Name</returns>
        private string GetColNo(string cellAddr)
        {
            string alphabet = String.Empty;
            string digit = String.Empty;
            Match regexMatch = Regex.Match(cellAddr, "\\d");
            if (regexMatch.Success)
            //Found numeric part in the coverage string{
            {
                int digitStartIndex = regexMatch.Index;
                //Get the index where the numeric digit found	
                alphabet = cellAddr.Substring(0, digitStartIndex);
            }
            return alphabet;
        }


        /// <summary>
        /// Gets the column and row information from the range
        /// </summary>
        /// <param name="strRange">range name</param>
        /// <returns>cell and sheet details</returns>
        private ArrayList GetColumnInfo(string strRange)
        {
            ArrayList arrColumnInfolist = new ArrayList();
            string strSheetName = string.Empty;
            string strStartcol = string.Empty;
            string strStartRowNum = string.Empty;
            string strEndcol = string.Empty;
            string strLastRowNum = string.Empty;
            string strtempFirst = string.Empty;
            string strtempSec = string.Empty;
            string[] charArray = null;

            charArray = strRange.Split('!');

            strSheetName = Convert.ToString(charArray[0], CultureInfo.CurrentCulture);

            strtempFirst = Convert.ToString(charArray[1], CultureInfo.CurrentCulture);

            charArray = strtempFirst.Split(':');

            if (Convert.ToString(charArray[0], CultureInfo.CurrentCulture) != null)
                strtempFirst = Convert.ToString(charArray[0], CultureInfo.CurrentCulture);
            strtempFirst = strtempFirst.Trim();

            if (Convert.ToString(charArray[1], CultureInfo.CurrentCulture) != null)
                strtempSec = Convert.ToString(charArray[1], CultureInfo.CurrentCulture);
            strtempSec = strtempSec.Trim();

            charArray = strtempFirst.Split('$');

            if (Convert.ToString(charArray[1], CultureInfo.CurrentCulture) != null)
                strStartcol = Convert.ToString(charArray[1], CultureInfo.CurrentCulture);

            if (Convert.ToString(charArray[2], CultureInfo.CurrentCulture) != null)
                strStartRowNum = Convert.ToString(charArray[2], CultureInfo.CurrentCulture);

            charArray = strtempSec.Split('$');

            if (Convert.ToString(charArray[1], CultureInfo.CurrentCulture) != null)
                strEndcol = Convert.ToString(charArray[1], CultureInfo.CurrentCulture);

            if (Convert.ToString(charArray[2], CultureInfo.CurrentCulture) != null)
                strLastRowNum = Convert.ToString(charArray[2], CultureInfo.CurrentCulture);

            arrColumnInfolist.Add(strStartcol);
            arrColumnInfolist.Add(strStartRowNum);
            arrColumnInfolist.Add(strEndcol);
            arrColumnInfolist.Add(strLastRowNum);
            arrColumnInfolist.Add(strSheetName);

            return arrColumnInfolist;

        }

        /// <summary>
        /// Get the data for the region name passed
        /// </summary>
        /// <param name="regionname"></param>
        private DataTable GetRangeData(string regionname)
        {
            ArrayList arrColumnInfolist = null;
            if (document == null)
            {
                document = SpreadsheetDocument.Open(m_package);
            }
            var wbPart = document.WorkbookPart;
            DefinedNames definedNames = wbPart.Workbook.DefinedNames;

            if (definedNames != null)
            {
                foreach (DefinedName dn in definedNames)
                {
                    if (dn.Name.Value == regionname)
                    {
                        arrColumnInfolist = GetColumnInfo(dn.Text.ToString());
                        break;
                    }
                }
            }

            int startColNumber = ColtoNum(Convert.ToString(arrColumnInfolist[0], CultureInfo.CurrentCulture));
            int fisrtRow = Convert.ToInt32(arrColumnInfolist[1], CultureInfo.CurrentCulture);
            int endColNumber = ColtoNum(Convert.ToString(arrColumnInfolist[2], CultureInfo.CurrentCulture));
            int LastRow = Convert.ToInt32(arrColumnInfolist[3], CultureInfo.CurrentCulture);
            string sheet = Convert.ToString(arrColumnInfolist[4], CultureInfo.CurrentCulture);
            DataTable dtData = GetRangeData(fisrtRow, startColNumber, LastRow, endColNumber, sheet);
            return dtable;
        }

        /// <summary>
        /// get the numeric representation of the Column name
        /// </summary>
        /// <param name="columnName"></param>
        /// <returns></returns>
        private int ColtoNum(string columnName)
        {
            int sum = 0; int exponent = 0;
            for (int i = columnName.Length - 1; i >= 0; i--)
            {
                sum += (columnName[i] - 'A' + 1) * (GetPower(26, exponent));
                exponent++;
            }

            return sum;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="number"></param>
        /// <param name="exponent"></param>
        /// <returns></returns>
        private int GetPower(int number, int exponent)
        {
            int power = 1;
            for (int i = 0; i < exponent; i++)
                power *= number; return power;
        }

        /// <summary>
        /// Gets the range data for the cell range passed
        /// </summary>
        /// <param name="row"></param>
        /// <param name="col"></param>
        /// <param name="enrow"></param>
        /// <param name="encol"></param>
        /// <param name="sheetid"></param>
        /// <returns></returns>
        public DataTable GetRangeData(int row, int col, int enrow, int encol, string sheetid)
        {
            dtable = new DataTable();
            //Console.WriteLine("Prog Start " + DateTime.Now);

            if (nsMgr == null)
            {
                XmlDocument xdc = new XmlDocument();
                nsMgr = new XmlNamespaceManager(xdc.NameTable);
                nsMgr.AddNamespace("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
                nsMgr.AddNamespace("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
                nsMgr.AddNamespace("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
                nsMgr.AddNamespace("def", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
            }

            string sheetLoc = GetSheetId(sheetid);
            Uri sheet1Uri =
                   new Uri(@"/xl/" + sheetLoc, UriKind.Relative);

            return GetData(row, col, enrow, encol, sheet1Uri, nsMgr);

        }

        /// <summary>
        /// Gets the column name from the Numeric value
        /// </summary>
        /// <param name="index"></param>
        /// <returns></returns>
        private string GetColumnNameForReading(int index)
        {
            index -= 1; //adjust so it matches 0-indexed array rather than 1-indexed column

            int quotient = index / 26;
            if (quotient > 0)
                return GetColumnNameForReading(quotient) + chars[index % 26].ToString();
            else
                return chars[index % 26].ToString();
        }

        /// <summary>
        /// 
        /// </summary>
        private char[] chars = new char[] { 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z' };

        /// <summary>
        /// Get the shared string for from the shared string XML
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        private string GetSharedString(string value)
        {

            if (document == null)
            {
                document = SpreadsheetDocument.Open(m_package);
            }
            WorkbookPart wbPart = document.WorkbookPart;
            var stringTable = wbPart.
                                GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
            if (stringTable != null)
            {
                value = stringTable.SharedStringTable.ElementAt(int.Parse(value)).InnerText;
            }


            return value;
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public string GetSharedString(MemoryStream fileName, string value)
        {

            using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, false))
            {
                WorkbookPart wbPart = document.WorkbookPart;
                var stringTable = wbPart.
                                    GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
                if (stringTable != null)
                {
                    value = stringTable.SharedStringTable.ElementAt(int.Parse(value)).InnerText;
                }
            }

            return value;
        }

        /// <summary>
        /// Gets the range data for the cell details passed
        /// </summary>
        /// <param name="strow"></param>
        /// <param name="stcol"></param>
        /// <param name="enrow"></param>
        /// <param name="encol"></param>
        /// <param name="sheet1Uri"></param>
        /// <param name="nsMgr"></param>
        /// <returns></returns>
        private DataTable GetData(int strow, int stcol, int enrow, int encol,
            Uri sheet1Uri, XmlNamespaceManager nsMgr)
        {
            dtable = new DataTable();

            for (int jIndex = stcol; jIndex <= encol; jIndex++)
            {
                dtable.Columns.Add(GetColumnNameForReading(jIndex));
            }

            Hashtable ht = new Hashtable();
            string key = string.Empty;
            for (int i = stcol; i <= encol; i++)
            {
                key = GetColumnNameForReading(i);
                if (key != string.Empty)
                {
                    ht.Add(key.ToUpper(), key.ToUpper());
                }
            }

            XPathDocument sheet1Xml = GetReadOnlyPart(sheet1Uri);
            string s = "";
            bool sharedstr = false;

            int rowCount = strow;

            Hashtable htShared = GetSharedString();
            string exp = "/def:worksheet/def:sheetData/def:row[@r >=" + strow + " and @r <= " + enrow + "]";
            XPathNavigator navigator = sheet1Xml.CreateNavigator();
            XPathNodeIterator NodeIter = navigator.Select(exp, nsMgr);
            DataRow dr = null; string col = ""; string s2 = "";
            while (NodeIter.MoveNext())
            {
                s = "";
                dr = dtable.NewRow();
                NodeIter.Current.MoveToFirstChild();

                if (NodeIter.Current.GetAttribute("r", "") != "")
                {
                    col = GetColNo(NodeIter.Current.GetAttribute("r", ""));
                    if (ht.ContainsKey(col))
                    {
                        if (NodeIter.Current.GetAttribute("t", "").Trim() == "" || NodeIter.Current.GetAttribute("t", "").Trim() == "str" || NodeIter.Current.GetAttribute("t", "").Trim() == "e")
                        {
                            sharedstr = false;
                        }
                        else
                        {
                            sharedstr = true;
                        }

                        if (NodeIter.Current.MoveToFirstChild())
                        {
                            if (NodeIter.Current.Name == "f")
                            {
                                if (NodeIter.Current.MoveToNext())
                                {
                                }
                            }

                            s2 = NodeIter.Current.Value;

                            if (sharedstr)
                            {

                                if (!htShared.ContainsKey(s2))
                                {
                                    s = GetSharedString(s2);
                                    htShared.Add(s2, s);
                                }
                                else
                                {
                                    s = htShared[s2].ToString();
                                }
                            }
                            else
                            {
                                s = s2;
                            }
                            NodeIter.Current.MoveToParent();
                        }
                        dr[col] = s;
                    }
                    while (NodeIter.Current.MoveToNext())
                    {
                        s = "";
                        if (NodeIter.Current.GetAttribute("r", "") != "")
                        {
                            col = GetColNo(NodeIter.Current.GetAttribute("r", ""));
                            if (ht.ContainsKey(col))
                            {
                                if (NodeIter.Current.GetAttribute("t", "").Trim() == "" || NodeIter.Current.GetAttribute("t", "").Trim() == "str" || NodeIter.Current.GetAttribute("t", "").Trim() == "e")
                                {
                                    sharedstr = false;
                                }
                                else
                                {
                                    sharedstr = true;
                                }

                                if (NodeIter.Current.MoveToFirstChild())
                                {
                                    if (NodeIter.Current.Name == "f")
                                    {
                                        if (NodeIter.Current.MoveToNext())
                                        {
                                        }
                                    }

                                    s2 = NodeIter.Current.Value;

                                    if (sharedstr)
                                    {
                                        if (!htShared.ContainsKey(s2))
                                        {
                                            s = GetSharedString(s2);
                                            htShared.Add(s2, s);
                                        }
                                        else
                                        {
                                            s = htShared[s2].ToString();
                                        }
                                    }

                                    else
                                    {
                                        s = s2;
                                    }
                                    NodeIter.Current.MoveToParent();
                                }
                                dr[col] = s;
                            }
                        }
                    }


                    /// #N/A Check and remove the rows.

                    dtable.Rows.Add(dr);
                }
            }


            return dtable;
        }
        #endregion


        /// <summary>
        /// This method give count of the number of record in excel sheet
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="strSheetName"></param>
        /// <returns></returns>
        public int GetNumberOfRecord(string fileName, string strSheetName)
        {

            var iRows = 0;

            try
            {
                using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open(fileName, false))
                {
                    WorkbookPart workbookPart = myDoc.WorkbookPart;
                    IEnumerable<Sheet> sheets =
                        myDoc.WorkbookPart.Workbook.GetFirstChild<Sheets>()
                             .Elements<Sheet>()
                             .Where(s => s.Name == strSheetName);
                    if (sheets.Count() == 0)
                    {
                        return iRows;
                    }
                    string relationshipId = sheets.First().Id.Value;
                    WorksheetPart worksheetPart = (WorksheetPart)myDoc.WorkbookPart.GetPartById(relationshipId);

                    //SheetData sheetData = worksheetPart.Worksheet.Elements<SheetData>().First();
                    SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
                    string value;
                    var i = 1;
                    var j = 0;

                    iRows = sheetData.Elements<Row>().Count();
                }
            }
            catch (Exception ex)
            {
                throw ex;
                //ExceptionManager.Publish(ex);
            }

            return iRows;

        }
        /// <summary>
        /// Validate the sheet Name
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="strSheetName"></param>
        /// <returns></returns>
        public bool ValidateSheetName(string fileName, string strSheetName)
        {

            var strRetName=string.Empty;

            try
            {
                using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open(fileName, false))
                {
                    WorkbookPart workbookPart = myDoc.WorkbookPart;
                    IEnumerable<Sheet> sheets =
                        myDoc.WorkbookPart.Workbook.GetFirstChild<Sheets>()
                             .Elements<Sheet>()
                             .Where(s => s.Name == strSheetName);
                    if (sheets.Count() == 0)
                    {
                        return false;
                    }
                    
                }
            }
            catch (Exception ex)
            {

                //ExceptionManager.Publish(ex);
                throw ex;
            }

            return true;

        }

        /*----------------------------------Extended Method by APP Team---------------------*/
        /*Extended by Risk Management Team--------------------------------------------------*/
        public DataTable GetDataInDataSet(int ColumnBeginIndex,int ColumnEndIndex,int BeginRowIndex,int EndRowIndex,bool FirstRowHeader=false)
        {

            if (ColumnBeginIndex > ColumnEndIndex)
            {
                throw new Exception("Begin Column Index cannot be greater than End Index");
            }
            DataTable dt = new DataTable("Data");
            for (int i = ColumnBeginIndex; i <= ColumnEndIndex; i++)
            {
                if (FirstRowHeader)
                {
                    //If the first row is header, take the first row as Datatable title
                    dt.Columns.Add(Cell(GetColumnName(i) + BeginRowIndex.ToString()));
                }
                else
                {
                    dt.Columns.Add(new DataColumn(GetColumnName(i)));
                }
            }

            if (EndRowIndex == 0)
                EndRowIndex = _sheetData.Elements<Row>().Count();

            //if the first row is header, take the first row as DataTable Title
            int columnCounter = 0;


            if (BeginRowIndex > EndRowIndex)
            {
                throw new Exception("Begin Row Index cannot be greater than End Index");
            }
            for (int j = BeginRowIndex+(FirstRowHeader==true?1:0); j <= EndRowIndex; j++)
           
            {
                DataRow dr = dt.NewRow();
                for (int k = 0; k < dt.Columns.Count; k++)
                {
                  
                   
                        dr[k] = Cell(GetColumnName(k+ColumnBeginIndex) + j.ToString());
                  
                   // dr[k] = Cell(dt.Columns[k].ColumnName + j.ToString());

                }
                dt.Rows.Add(dr);

            }

            return dt;

        }
        

        public DataTable GetDataInDataSet(int ColumnBeginIndex, int ColumnEndIndex, int BeginRowIndex, int EndRowIndex,int[] paDateColumns,int [] paNumericColumns)
        {

            if (ColumnBeginIndex > ColumnEndIndex)
            {
                throw new Exception("Begin Column Index cannot be greater than End Index");
            }
            DataTable dt = new DataTable("Data");
            for (int i = ColumnBeginIndex; i <= ColumnEndIndex; i++)
            {
                dt.Columns.Add(new DataColumn(GetColumnName(i)));

            }
            if (EndRowIndex == 0)
                EndRowIndex = _sheetData.Elements<Row>().Count();
            if (BeginRowIndex > EndRowIndex)
            {
                throw new Exception("Begin Row Index cannot be greater than End Index");
            }
            for (int j = BeginRowIndex; j <= EndRowIndex; j++)
            {
                DataRow dr = dt.NewRow();
                for (int k = 0; k < dt.Columns.Count; k++)
                {
                    string dtValue = Cell(dt.Columns[k].ColumnName + j.ToString());
                    if (paDateColumns.Contains(k + 1))
                    {
                        
                        dr[k] = DateTime.FromOADate(Convert.ToDouble(dtValue)).ToString();
                    }
                    else if (paNumericColumns.Contains(k + 1)&& !String.IsNullOrEmpty(dtValue))
                    {
                        dr[k]=Math.Round(Convert.ToDouble(dtValue),3).ToString();
                    }
                    else
                    {
                        dr[k] = dtValue;
                    }
                }
                dt.Rows.Add(dr);

            }

            return dt;

        }

        public string GetCellData(int Column,int Row)
        {
             return this.Cell(GetColumnName(Column) + Row.ToString());
        }
    }




}
